Page Comparison

Configuration in repository.cfg

Code Block

language	none

<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

language	none

<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

language	none

<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 on one record per access, defines acces modul and access data

Expand

title	Table 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 record records per access, contains details according to configuration

Expand

title	Table 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

language	sql

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

...

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

language	sql

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

language	sql

  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%'

Versions Compared

Old Version 4

New Version Current

Key

Configuration in repository.cfg

H2

Oracle

MSSQL

Tables:

Example Queries (based on MSSQL)