MOSS 列表的数据库存储结构

MOSS列表如何保存在数据库中的，网上没有详细的说明，自己摸索了一下，大多数常用的字段都查询了出来，如下Sql所示，查询出一个自定义列表的各种字段

SELECT     nvarchar1 AS 编号, ntext2 AS 清单描述, nvarchar3 AS 提出方, nvarchar4 AS 提出人, datetime1 + 0.3334 AS 提出日期, nvarchar5 AS 所属小组,
                      datetime2 + 0.3334 AS 要求解决日期, nvarchar6 AS 状态, nvarchar7 AS 处理人, datetime3 + 0.3334 AS 实际解决日期,
                      ntext3 AS 问题处理结果,dbo.UserConvertVertToHorz(nvarchar1) as 责任人 
FROM         dbo.AllUserData
WHERE     (tp_DirName = 'sites/css/Lists/VSS1') AND (tp_IsCurrent = 'True') AND (tp_ID NOT IN
                          (SELECT     ListItemId
                            FROM          dbo.RecycleBin
                            WHERE      (DirName = 'sites/css/Lists/VSS1')))

 

以上Sql需要在 WSS_Content 数据库中运行，字段对应只能是一个个在数据库里去找了。以上方法要注意，“责任人”字段是一个多用户的字段，值没有直接存放在 AllUserData 表中，而是存放在AllUserData表中，靠AllUserDataJunctions表把两个表连接起来，所以写了一个 UserConvertVertToHorz 方法把多个用户转化成 User1,User2的格式，如下是UserConvertVertToHorz方法的代码：

 

USE [WSS_Content]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[UserConvertVertToHorz](@Col1Val nvarchar(500))
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @RetVal nvarchar(1000)
SET @RetVal = ''
-- 通过递归 SELECT 连接指定列存储到临时变量中
SELECT @RetVal = UserName + ',' + @RetVal FROM problemUser
WHERE ProblemCode = @Col1Val
-- 去掉尾巴的 , (逗号)
IF LEN(@RetVal) > 0
SET @RetVal = LEFT(@RetVal, LEN(@RetVal) - 1)

RETURN @RetVal
END

方法里用到了problemUser视图，视图代码如下：

ALTER VIEW [dbo].[ProblemUser]
AS
SELECT     a.nvarchar1 AS ProblemCode, c.tp_Title AS UserName
FROM         dbo.AllUserData AS a LEFT OUTER JOIN
                      dbo.AllUserDataJunctions AS b ON a.tp_LeafName = b.tp_LeafName LEFT OUTER JOIN
                      dbo.UserInfo AS c ON b.tp_Id = c.tp_ID
WHERE     (a.tp_DirName = 'sites/css/Lists/VSS1')
AND (b.tp_DirName = 'sites/css/Lists/VSS1')
AND (a.tp_ID NOT IN
                          (SELECT     ListItemId
                            FROM          dbo.RecycleBin
                            WHERE      (DirName = 'sites/css/Lists/VSS1')))