topic Re: Database Schema in Alfresco Archive

Database Schema

mehul1511 — Thu, 21 Apr 2011 03:09:26 GMT

Hi,Is there a database schema other then alfresco core table PNG!! I am having hard time understanding the jbpm and avm tables. If there is a way I can access that schema, please let me know..Thanks in advance..

Re: Database Schema

mikeh — Thu, 21 Apr 2011 07:37:30 GMT

There are some tools that can do this.

If you're on a Mac: http://www.malcolmhardie.com/sqleditor/
Otherwise: http://code.google.com/p/database-diagram/

Mike

Re: Database Schema

mehul1511 — Thu, 21 Apr 2011 15:43:26 GMT

Thank you for the reply, but I was not able to dump the sql schema as there there is many confusing DML in .sql files of alfresco. Please let me know if there is just a default image of the whole database schema other then alfresco core tables offcourse.

Thank you

Re: Database Schema

viron82 — Fri, 10 Jun 2011 10:16:49 GMT

Here are the database schema that I received. I created a database dump and import it into Power Disagner. Version 3.4.d (win32).
I propose to begin a discussion about the purpose of the tables. Any thoughts please?

I do not know how to attach a file, so I posted it in filehosting

[Admin: I've deleted that URL - my virus detection objected ]

Re: Database Schema

mrogers — Fri, 10 Jun 2011 10:25:50 GMT

Alfresco does not publish database schema information since the implementation is considered to be private to the implementation of Alfresco.

Also the implementation details change frequently so as and when you upgrade your applications will break.

What you should be doing is accessing Alfresco via one of the many public APIs.

Re: Database Schema

viron82 — Fri, 10 Jun 2011 10:35:12 GMT

Ok. Could you tell me links that describe the API or links that will help me understand how to use API for working with database Alfresco. I need to create a table and work with it. Where do I start using the database?

Re: Database Schema

mrogers — Fri, 10 Jun 2011 10:40:53 GMT

It may help if you say what you are trying to achieve before worrying about implementation.

Re: Database Schema

viron82 — Fri, 10 Jun 2011 11:36:31 GMT

I want to implement functionality digital signature. I think the store digital signature and a link to the signed document (file) in the database. And by clicking on the "verify a digital signature for the current document (file) to receive from a database appropriate scientific digital signature.

Re: Database Schema

mrogers — Fri, 10 Jun 2011 11:47:44 GMT

The "normal" place to store information relating to documents is as properties of the node. You should probably be storing the signature in a property of a node. How its persisted is up to alfresco.

Re: Database Schema

viron82 — Fri, 10 Jun 2011 12:04:10 GMT

Yes in the properties I will specify that the file (document) has a digital signature, but it is a digital signature (to be more precise information about the digital signature, digital signature, ie, content) is stored separately, I think to put it into the database. So what I need to store information about how many people signed the document at what time and from which IP address. I decided to keep it in a database table.

Re: Database Schema

viron82 — Fri, 10 Jun 2011 12:06:39 GMT

And where are stored property finally?

Re: Database Schema

mrogers — Fri, 10 Jun 2011 12:13:51 GMT

So have another content node containing the signature and create an assoc between the document and the signature.

There's no need for you to touch the DB.

Re: Database Schema

viron82 — Wed, 15 Jun 2011 08:07:06 GMT

Is it true that the properties file is stored in the database?
Whether correct to store a digital signature signed file directly to the property of the current file? Is there a limit on the size of the contents of the field properties?
And a few more questions: do I need to pass a digital signature from the client (for that would not transfer the private key on the network) which API I can use to work with properties. How do I write the value of a property from a client? Please answer.

Re: Database Schema

mrogers — Wed, 15 Jun 2011 11:01:17 GMT

->Is it true that the properties file is stored in the database?
Yes
->Whether correct to store a digital signature signed file directly to the property of the current file?
Depends upon your requirements.
-> Is there a limit on the size of the contents of the field properties?
Depends upon the type of your property and the DB.   MySql is 64K for a string.
->And a few more questions: do I need to pass a digital signature from the client (for that would not transfer the private key on the network) which API I can use to work with properties.
Depends upon the architecture of your solution.   I would not reccomend transfer of a PK.
->How do I write the value of a property from a client? Please answer.
Depends on where you are accessing alfresco from.   The primary ways are the Java or JavaScript APIs for local access or web scripts for remote access.   For example if there is a "sign" action within alfresco then use the NodeService.