Versions Compared

Old Version 1

changes.mady.by.user Fabian Seifert

Saved on

compared with

New Version Current

changes.mady.by.user Fabian Seifert

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Configuration in

...

repository.cfg

Code Block
languagenone
<repoName>.protocolservice.type=database
<repoName>.protocolservice.database.driver=
<repoName>.protocolservice.database.url=
<repoName>.protocolservice.database.user=
<repoName>.protocolservice.database.password=
#<repoName>.protocolservice.database.read-connections.max= #default: 32, not after 2.7.0
#<repoName>.protocolservice.database.read-connections.min= #default: 32, not after 2.7.0
#<repoName>.protocolservice.database.read-connections.shared= #default: false, not after 2.7.0
#<repoName>.protocolservice.database.write-connections.max= #default: 32, not after 2.7.0
#<repoName>.protocolservice.database.write-connections.min= #default: 32, not after 2.7.0
#<repoName>.protocolservice.database.cache-statements= #default: false, not after 2.7.0
#<repoName>.protocolservice.database.cache-statements.size= #default: 50

...

, not after 2.7.0
#<repoName>.protocolservice.database.minimumIdle= #default: 10 minimumPoolSize
#<repoName>.protocolservice.database.maximumPoolSize= #default: 10 maximumPoolSize
#<repoName>.protocolservice.database.connectionTimeout= #default: 30000 duration after a connection lease request will time out
#<repoName>.protocolservice.database.idleTimeout= #default: 600000 duration after idle connection will be closed
#<repoName>.protocolservice.database.keepaliveTime= #default: 0
#<repoName>.protocolservice.database.maxLifetime= #default: 1800000
#<repoName>.protocolservice.database.scripts= #default: classpath:sql/prot/migration/h2|sqlserver|oracle, dependening on database provider , 3.0.0+
Info

As of version 2.7.0 we switched from EclipseLink to Hibernate, therefore some parameters have changed.

Info

As of version 3.4.0 the connection pool handling was improved. It allows using a smaller number of connections, i.e. changing the parameter minimumIdle to 1 should lower the utilization of the database without significant impact on overall performance.

H2

Code Block
languagenone
<repoName>.protocolservice.database.driver=org.h2.Driver
<repoName>.protocolservice.database.url=jdbc:h2:C:/h2db.db;FILE_LOCK=FS;TRACE_LEVEL_SYSTEM_OUT=0

Oracle

Code Block
languagenone
<repoName>.protocolservice.database.driver=oracle.jdbc.OracleDriver
<repoName>.protocolservice.database.url=jdbc:oracle:thin:@localhost:49161:xe

MSSQL

Code Block
<repoName>.protocolservice.database.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
<repoName>.protocolservice.database.url=jdbc:sqlserver://localhost:1433;databaseName=CONTENTSERVER;

Required Configuration for Searching in Protocols:

Make sure that your configuration has the following values:

Code Block

FI.protocolservice.document.properties=name,contentlength,path,contentDigest
FI.protocolservice.container.properties= name,path

Supported values for document:
starttime,endtime,requestid,path,id,respository,status,message,command,object,module,client,user,objectcommand,objectid,contentLength,contentType,contentDisposition,contentDigest,contentDigestAlgorithm,contentEncoding,creationTime,modificationTime,creationUser,modificationUser

Supported values for container:

name,path

Tables:

PROTOCOL

Contains one record per access, defines acces modul and access data

Expand
titleTable create Scripted (based on MS SQL)

CREATE TABLE [dbo].[PROTOCOL](
[RECORDID] [numeric](19, 0) NOT NULL,
[CLIENT] varchar NULL,
[COMMANDCATEGORY] varchar NULL,
[COMMANDMESSAGE] varchar NULL,
[COMMANDNAME] varchar NULL,
[COMMANDOBJECT] varchar NULL,
[COMMANDSTATUS] varchar NULL,
[COMMANDSTATUSCODE] varchar NULL,
[ENDTIME] datetime2 NULL,
[MODULENAME] varchar NULL,
[NODENAME] varchar NULL,
[OBJECTID] varchar NULL,
[REPOSITORYNAME] varchar NOT NULL,
[REQUESTID] varchar NOT NULL,
[STARTTIME] datetime2 NOT NULL,
[USERNAME] varchar NULL,
[VERSION] [int] NULL,
PRIMARY KEY CLUSTERED
(
[RECORDID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

PROTKEY_VALUES

multiple records per access, contains details according to configuration

Expand
titleTable create script (based on MSSQL)

CREATE TABLE [dbo].[PROTKEY_VALUES](
[ID] [numeric](19, 0) NOT NULL,
[FIELDBOOLVALUE] [bit] NULL,
[FIELDDATETIMEVALUE] datetime2 NULL,
[FIELDDECIMALVALUE] [numeric](28, 0) NULL,
[FIELDINTVALUE] [numeric](28, 0) NULL,
[FIELDSTRINGVALUE] varchar NULL,
[TYPEID] varchar NOT NULL,
[RECORDID] [numeric](19, 0) NOT NULL,
[FIELDID] varchar NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PROTKEY_VALUES] ADD DEFAULT ((0)) FOR [FIELDBOOLVALUE]
GO

ALTER TABLE [dbo].[PROTKEY_VALUES] WITH CHECK ADD CONSTRAINT [PROTKEY_VALUESRECORDID] FOREIGN KEY([RECORDID])
REFERENCES [dbo].[PROTOCOL] ([RECORDID])
GO

ALTER TABLE [dbo].[PROTKEY_VALUES] CHECK CONSTRAINT [PROTKEY_VALUESRECORDID]
GO

Example Queries (based on MSSQL)

find all Entries

Code Block
languagesql
SELECT p.*, pv.FIELDSTRINGVALUE as 'Path'
  FROM [PROTOCOL] p
  inner join PROTKEY_VALUES pv on p.RECORDID = pv.RECORDID
  where pv.FIELDID = 'path'

find read entries only

you can replace “read“ with the following values to get only certain categories:

Value

Description

read

all read access to a folder or document (only CMIS/ILM)

create

all create folder or document entries

update

all update requests

delete

all delete requests for folder or documents

info

for info requests (AricheLink or ILM)

admin

all admin access like create put certificate or serverInfo

Code Block
SELECT p.*, pv.FIELDSTRINGVALUE as 'Path'
  FROM [PROTOCOL] p
  inner join PROTKEY_VALUES pv on p.RECORDID = pv.RECORDID
  where pv.FIELDID = 'path' 
  and p.COMMANDCATEGORY = 'read'

ArchiveLink based Queries

All protocols ArchiveLink entries:

Protocols for documents and components create seperate protocoll entries. A “Create” exists for creation of the document and for each component. Same applies for other commands.

COMMANDOBJECT

Description

document

Protocols for the ArchiveLink Component

container

Protocol for the ArchiveLink Document

Code Block
languagesql
select CLIENT,COMMANDOBJECT,COMMANDMESSAGE, COMMANDCATEGORY,COMMANDSTATUS,STARTTIME,ENDTIME,MODULENAME,REPOSITORYNAME,REQUESTID,
  (select FIELDSTRINGVALUE from PROTKEY_VALUES pv inner join PROTOCOL prot on PROT.RECORDID = pv.RECORDID and prot.REQUESTID = P.REQUESTID where FIELDID = 'path' and prot.COMMANDOBJECT = 'document') as DOCUMENT,
  (select FIELDSTRINGVALUE from PROTKEY_VALUES pv inner join PROTOCOL prot on PROT.RECORDID = pv.RECORDID and prot.REQUESTID = P.REQUESTID where FIELDID = 'name' and prot.COMMANDOBJECT = 'document') as COMPONENT,
  (select FIELDINTVALUE from PROTKEY_VALUES pv inner join PROTOCOL prot on PROT.RECORDID = pv.RECORDID and prot.REQUESTID = P.REQUESTID where FIELDID = 'contentlength' and prot.COMMANDOBJECT = 'document') as SIZE,
   (select FIELDSTRINGVALUE from PROTKEY_VALUES pv inner join PROTOCOL prot on PROT.RECORDID = pv.RECORDID and prot.REQUESTID = P.REQUESTID where FIELDID = 'contentDigest' and prot.COMMANDOBJECT = 'document') as HASHVALUE
  from Protocol p
  where MODULENAME = 'ContentServer4ArchiveLink'

Filter based on docID:

Enter docID instead of example value 85DCD9A03683496DB7B9A8BFED0EBEAD

Code Block
languagesql
  select*,  CLIENT,COMMANDOBJECT,COMMANDMESSAGE, COMMANDCATEGORY,COMMANDSTATUS,STARTTIME,ENDTIME,MODULENAME,REPOSITORYNAME,REQUESTID, pv.FIELDSTRINGVALUE as 'PATH',
  (select FIELDINTVALUE from PROTKEY_VALUES pv inner join PROTOCOL prot on PROT.RECORDID = pv.RECORDID and prot.REQUESTID = P.REQUESTID where FIELDID = 'contentlength' and prot.COMMANDOBJECT = 'document') as SIZE,
   (select FIELDSTRINGVALUE from PROTKEY_VALUES pv inner join PROTOCOL prot on PROT.RECORDID = pv.RECORDID and prot.REQUESTID = P.REQUESTID where FIELDID = 'contentDigest' and prot.COMMANDOBJECT = 'document') as HASHVALUE
  from Protocol p
   inner join PROTKEY_VALUES pv on p.RECORDID = pv.RECORDID and  pv.FIELDID = 'path'
  where pv.FIELDID = 'path'
  and MODULENAME = 'ContentServer4ArchiveLink'
  and pv.FIELDSTRINGVALUE like '%85DCD9A03683496DB7B9A8BFED0EBEAD%'