MySQL database and Alfresco

[zsoltbalint]

Hi.
My name is Zsolt and I have a problem with alfresco that I can't resolve for days now.
I'm using alfresco 4.0.2 (I know it's not the latest but I have to use this) and my task is to create a new user group. A searched the documentation of Alfresco and find how to create a new user group via the page, but I have to create it in the back-end. I managed to create it but the main problem is that it did not appear in the database. It is the same when I create the group via the page. I checked the alf_authority table and there are some groups predefined and users but my newly created sub-group is not there. The interesting thing is when I create a new user it will be put in the db without any problem.
So there must be some configuration error that I miss, maybe it didn't trigger an update of the db if a new group is created?
Can someone who is more familiar with the flow between the mysql db and alfresoc explain the process.   

[steven_okennedy]

Hi Zsolt,

May I ask why you are looking at the alf_authority table?  Is this just an assumption that this where you think it should go, or are you intending to interrogate this table using SQL queries?

Firstly, just because a group doesn't exist in this table, doesn't mean it wasn't created properly.  Alfresco groups are represented as nodes in the system (just like pretty much everything else - users, documents, folders, rules, sites, document libraries etc) so when you create a group you actually get a new node in the alf_node table.  I'm not sure when an entry gets created in the alf_authority table, I've never looked into it - there are other built in groups that don't exist in this table e.g. the swsdp site that comes bundled with the standard installation has a built-in group called GROUP_site_swsdp which you won't see in alf_authority either - it doesn't mean it doesn't exist though.

Generally Alfresco have designed the database schema as pretty much a black box, so they don't publish a schema or any real documentation on how the tables are used by the system - database queries are generally not considered supported extension points.  Of course, it's open source so you can go have a look if you like.  Find the DAO class for AlfAuthority and see what services use it and for what.

However, there's a much easier option.  Alfresco comes shipped with a Node Browser as part of the Admin Tools that allows you to browse through the objects and relationships for pretty much all nodes in the system.  Sites, documents and folders as well as the Data Dictionary are held under the /app:company_home/ path.  System nodes such as authority zones, users and groups and workflow related nodes are held under the /sys:system path.

To look at all the groups in the system you navigate through to the /sys:system/sys:authorities path (click on / -> sys:system -> sys:authorities in the node browser).  This will include groups created by the system, created locally and even those synchronised from LDAP/AD if you have any of those authentication systems set up.


So to summarise, Alfresco uses the DB to store details every node in the system (as well as paths to where the physical content lives on the filesystem) but the schema is in quite a raw form - everything is a node, and its the content models built on top of that raw data that give the nodes their meaning and give the data form.  The content models provide an abstraction on top of the raw data that allow complex and flexible relationships to be managed without having to touch or modify the DB schema any time new properties or types are required.  The Node Browser is a tool provided out of the box to allow you to understand the structure of that abstraction without having to parse the raw data in the DB.

Regards

Steven

P.S.  I've never looked at why all authorities are not in Alf_Authority, you have me curious now around what that table is actually for…

[zsoltbalint]

Hi Steven!
Thanks for the information you provided. I'm going to check all what you said it is really useful.
Our newly generated users and the old groups were there in the alf_authority table that's why I thought there should be all the groups.
I checked the alf_node table there are a lot of data in it so probably my newly created group is there also.
I haven't heard the Node Browser but I'm going to check it.
I still didn't get something. How come that the newly created users get in the alf_authorityt table but the groups not, only after I dropped the db and started a new one? So my main problem is that I want that the code itself creates the new group not a user or admin via the browser, and it is only do this if I delete the entire database and create a new one. Do you have any idea how to solve this, so I don't have to delete my db and start again just for this?
Thank for the help Steven you deserve a cookie: https://encrypted-tbn1.gstatic.com/images?q=tbn:ANd9GcQDf16lseZUbZE8w5Fu6V2vn2Y4K6PAAXvP147Sl2WFntMP...