Hi Jason,

I found this maybe this can help you. There can maybe have been some changes to the version 7.1.1 I don´t know about this.

But just tried to help you here. http://our.umbraco.org/forum/core/general/6377-Umbraco-Database-Scheme-Diagram

Here is  a database schema created by Hendy Racher http://blog.hendyracher.co.uk/wp-content/uploads/Umbraco-Schema-with-Constraints-v2.gif

Hope this can help you in some way.

/Dennis

Please do NOT do things directly in the database, that's what we have APIs for. It's not a wonderfully constructed database with foreign keys all over the place and it is really, really easy to break things.

Let us know what the specifics are of what you want to do and we can point you to API methods to help you out.

Ps. The ContentService API would be a very good start: http://our.umbraco.org/documentation/Reference/Management-v6/Services/ContentService

I want to be able to call the web services of our desktop software outside of Umbraco and pull in pages and content based upon the content retrieved from the web services. In essence, some of the content in our site will not be created and managed through Umbraco and will need to be imported in each night via a background call to update the content. Also, with this in mind we must be able to create a new page using an existing document type if new content is added also.

Hi

@Sebastiaan

there are certain time direct database access is needed - for example, I need to clear up the mess my client has made in the back-end by creating many users - of course they can be disabled but they want them removed completely. I cant do that in the dashboard so need to dive into the tables and make sure to keep reassign content etc to keep integrity, hence it would be nice to have an up-to-date schema for reference.

Closest I've got so far, from another post is:

-- step 1, reassign all content back to main user

UPDATE umbracoNode SET nodeUser = 0 WHERE nodeUser > 0
UPDATE cmsDocument SET documentUser = 0 WHERE documentUser > 0

-- step 2, clear down the other users

DELETE FROM umbracoLog WHERE userId > 0
DELETE FROM umbracoUser2App WHERE [user] > 0
DELETE FROM umbracoUserLogins WHERE userID > 0
DELETE FROM umbracoUser WHERE id > 0

Trouble is, there's a table CMSTask that has parentUserId and userId but i cant find any documentation for this

PLEASE do not remove users from the DB unless you're 101% sure they never logged in and touched any content. The reason we don't allow deletions is that if a user ID is referenced somewhere we will assume the user still exists and you'll get horrible errors if the user is gome.

cmsTask has a foreign key to cmsUser which you can find through SQL management studio. cmsTask.userId has no foreign key definition.
Again, I would recommend you leave the users be. At least take a backup of your database.

@Sebastiaan

Thanks for that - while I appreciate that deleting users may be problematic, how would you advise removing them from the dashboard view in the user section and user picker - even when disabled they still appear, which is a nightmare, given the number of users created.

Basically I need to clear down the list - if not via sql, then how?

I've gone through the database and identified that tables that have a reference to a user

umbracoNode
cmsDocument
cmsTask
umbracoLog
umbracoUser2App
umbracoUserLogins
umbracoUser

and cmsContentXml (but i can truncate that and regenerate)

Given that list and a good knowledge of SQL, surely it's not unreasonable to delete one or more users, unless I'm missing something? Obviously taking a db backup first!

Can you look at the table list and advise where else (if any) the user is referenced?

Much appreciated

Ian

is working on a reply...