MOSS 2007: What is the source of "Directories"?

Question

Hello all,

I'm trying to generate a new SharePoint list item directly using SQL server. What's stopping me is damn tp_DirName column. I have no ideas how to create this value.

Just for instance, I have selected all tasks from AllUserData, and there are possible values for the column: 'MySite/Lists/Task', 'Lists/Task' and even 'MySite/Lists/List2'.

MySite is the FullUrl value from Webs table. I can obtain it. But what about 'Lists/Task' and '/Lists/List2'? Where they are stored?

If try to avoid SQL context, I can formulate it the following way: what is the object, that has such attribute as '/Lists/List2'? Where can I set it up in GUI?

Answer 1

Just a FYI. It is VERY not supported to try and write directly to SharePoint's SQL Tables. You should really try and write something that utilizes the SharePoint Object Model. Writing to the SharePoint database directly mean Microsoft will not support the environment.

Answer 2

I've discovered, that [AllDocs] table, in contrast to its title, contains information about "directories", that can be used to generate tp_DirName. At least, I've found "List2" and "Task" entries in [AllDocs].[tp_Leaf] column.

So the solution looks like this -- concatenate the following 2 components to get tp_DirName:

1. [Webs].[FullUrl] for the web, containing list, containing item.
2. [AllDocs].[tp_Leaf] for the list, containing item.

Concatenate the following 2 components to get tp_Leaf for an item:

1. (Item count in the list) + 1
2. '_.000'

Regards,

Answer 3

Well, my previous answer was not very useful, though it had a key to the magic. Now I have a really useful one.

Whatever they said, M$ is very liberal to the MOSS DB hackers. At least they provide the following documents:

http://msdn.microsoft.com/en-us/library/dd304112(PROT.13).aspx http://msdn.microsoft.com/en-us/library/dd358577(v=PROT.13).aspx

Read? Then, you know that all folders are listed in the [AllDocs] table with '1' in the 'Type' column.

Now, let's look at 'tp_RootFolder' column in AllLists. It looks like a folder id, doesn't it? So, just SELECT the single row from the [AllDocs], where Id = tp_RootFolder and Type = 1. Then, concatenate DirName + LeafName, and you will know, what the 'tp_DirName' value for a newly generated item in the list should be. That looks like a solid rock solution.

Now about tp_LeafName for the new items. Before, I wrote that the answer is (Item count in the list) + 1 + '_.000', that corresponds to the following query:

DECLARE @itemscount int;
SELECT @itemscount = COUNT(*) FROM [dbo].[AllUserData] WHERE [tp_ListId] = '...my list id...';
INSERT INTO [AllUserData] (tp_LeafName, ...) VALUES(CAST(@itemscount + 1 AS NVARCHAR(255)) + '_.000', ...)

Thus, I have to say I'm not sure that it works always. For items - yes, but for docs... I'll inquire into the question. Leave a comment if you want to read a report.

Answer 4

Hehe, there is a stored procedure named proc_AddListItem. I was almost right. MS people do the same, but instead of (count + 1) they use just... tp_ID :)

Anyway, now I know THE SINGLE RIGHT answer: I have to call proc_AddListItem.

UPDATE: Don't forget to present the data from the [AllUserData] table as a new item in [AllDocs] (just insert id and leafname, see how SP does it itself).