Query regarding referential integrity and performance issues

[itsard]

We are planning to integrate Alfresco DMS and RMS with our existing system.Our HR, Payroll and other common modules have a lot of complex applications.
Our concern is how would the relational database integrity be maintained in Alfresco database as it stores just the metadata for records
and the data being stored in the file system.Also, if there are too many records for an application, wont the metadata table
contain a lot many records affecting the performance in turn? and if we have to generate some complex reports on this data, where each field(Metadata) is stored in a other row,how it will affect the performance.

[jpotts]

Our concern is how would the relational database integrity be maintained in Alfresco database as it stores just the metadata for records
and the data being stored in the file system

Are you asking how Alfresco keeps the metadata it stores about content linked to the actual binaries on the file system? If so, this is Alfresco's job. You don't have to worry about it. You'll never let anyone touch Alfresco's data directory directly. They'll always go through Alfresco, either through the web client, FTP, webDAV, CIFS, SMTP, IMAP, etc. Alfresco keeps them tied together.

If you are asking how you might keep Alfresco content tied to records in your external systems, you'll have to do that yourself. You can have columns in those systems store the UUID of the object in Alfresco, or you can have a piece of metadata on the Alfresco object that stores a reference to the external systems, or both. Obviously, it will be up to you to make sure those stay in sync.

Also, if there are too many records for an application, wont the metadata table contain a lot many records affecting the performance in turn?

Yes, the more content you have in Alfresco, the more records you'll have in your Alfresco tables. For the most part, Alfresco, the ORM layer, and the database are going to work to keep everything humming along. With large datasets you'll want your DBA to see if he/she can tune performance by making database tweaks, just like you would with any other app that has a relational back-end.

if we have to generate some complex reports on this data, where each field(Metadata) is stored in a other row,how it will affect the performance

I wouldn't write reports against the Alfresco tables directly for two reasons: First, that schema isn't public. It could change without notice. (The auditing tables are the exception). Second, as you point out, you don't want to adversely affect the performance of your Alfresco server. A better approach would be to ETL that data out of the Alfresco tables and into another database and report against that.

Jeff

[itsard]

Thanks for your reply.
I have another query.
I am using the DMS WebServices API for uploading and manipulating the content and other things.
I wish to provide the username and password just once to start the session and use the ticket that is generated for subsequent calls.
Now the AuthenticationUtils.startSession method initiates the session but how do i provide the ticket to other subsequent calls
to validate if the ticket is valid.

[jpotts]

You can get the ticket at any time by calling AuthenticationUtils.getTicket. Note that you don't have to manage the ticket or ensure it is getting passed back in to subsequent calls–the AuthenticationUtils class sets up a callback handler that does that for you. If you want/need to do it yourself, you should be able to take a look at the source for AuthenticationUtils as an example.

Jeff

[itsard]

But I do need to pass the authentication details in the next service call right?
As, if I dont do that, it throws error of password callback failed. no ticket found.
I call the below method once.
AuthenticationDetails ad=AuthenticationUtils.startSession(uname,pwd);
I store this authentication details object in my session and
before every webservice call I set AuthenticationUtils.setAuthenticationDetails(ad);
so that the ticket passes correctly.