Protocol Database (tC)

Configuration in repository.cfg

<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+

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

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

<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

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

MSSQL

Required Configuration for Searching in Protocols:

Make sure that your configuration has the following values:

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

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

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

find read entries only

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

Value	Description

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

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

COMMANDOBJECT	Description
document	Protocols for the ArchiveLink Component
container	Protocol for the ArchiveLink Document

Filter based on docID:

Enter docID instead of example value 85DCD9A03683496DB7B9A8BFED0EBEAD