72 lines
2.0 KiB
Transact-SQL
72 lines
2.0 KiB
Transact-SQL
--Migration
|
|
DROP VIEW IF EXISTS V_ENTITYHISTORYBLOB
|
|
GO
|
|
|
|
CREATE VIEW V_ENTITYHISTORYBLOB AS
|
|
SELECT
|
|
e.ID
|
|
,e.C_ENTITYID
|
|
,e.C_ENTITYNAME
|
|
,e.C_ALTERATIONDATE
|
|
,e.C_REASON
|
|
,e.C_FELHASZNALOID
|
|
,e.C_INTEZMENYID
|
|
,e.C_TANEVID
|
|
,ev.C_ORIGINALVALUE
|
|
,ev.C_CURRENTVALUE
|
|
,ev.C_PROPERTYNAME
|
|
,COALESCE(f.C_NYOMTATASINEV, u.USERNAME) AS C_FELHASZNALONEV
|
|
,e.HOSTNAME AS C_HOSTNAME
|
|
FROM T_ENTITYHISTORY e WITH(NOLOCK)
|
|
INNER JOIN T_ENTITYBLOBVALUES ev WITH(NOLOCK) ON ev.C_ENTITYHISTORYID = e.ID
|
|
LEFT JOIN T_FELHASZNALO f WITH(NOLOCK) ON f.ID = e.C_FELHASZNALOID
|
|
LEFT JOIN auditlog.Users u WITH(NOLOCK) ON u.USERID = e.C_FELHASZNALOID
|
|
GO
|
|
|
|
DROP VIEW IF EXISTS V_ENTITYHISTORY
|
|
GO
|
|
|
|
CREATE VIEW V_ENTITYHISTORY AS
|
|
-- T_ENTITYBLOBVALUES
|
|
SELECT
|
|
e.ID
|
|
,e.C_ENTITYID
|
|
,e.C_ENTITYNAME
|
|
,e.C_ALTERATIONDATE
|
|
,e.C_REASON
|
|
,e.C_FELHASZNALOID
|
|
,e.C_INTEZMENYID
|
|
,e.C_TANEVID
|
|
,ev.C_ORIGINALVALUE
|
|
,ev.C_CURRENTVALUE
|
|
,ev.C_PROPERTYNAME
|
|
,COALESCE(f.C_NYOMTATASINEV, u.USERNAME) AS C_FELHASZNALONEV
|
|
,e.HOSTNAME AS C_HOSTNAME
|
|
FROM T_ENTITYHISTORY e WITH(NOLOCK)
|
|
INNER JOIN T_ENTITYBLOBVALUES ev WITH(NOLOCK) ON ev.C_ENTITYHISTORYID = e.ID
|
|
LEFT JOIN T_FELHASZNALO f WITH(NOLOCK) ON f.ID = e.C_FELHASZNALOID
|
|
LEFT JOIN auditlog.Users u WITH(NOLOCK) ON u.USERID = e.C_FELHASZNALOID
|
|
|
|
UNION ALL
|
|
|
|
-- T_ENTITYATTRIBUTEHISTORY
|
|
SELECT
|
|
e.ID
|
|
,e.C_ENTITYID
|
|
,e.C_ENTITYNAME
|
|
,e.C_ALTERATIONDATE
|
|
,e.C_REASON
|
|
,e.C_FELHASZNALOID
|
|
,e.C_INTEZMENYID
|
|
,e.C_TANEVID
|
|
,ea.C_ORIGINALVALUE
|
|
,ea.C_CURRENTVALUE
|
|
,ea.C_PROPERTYNAME
|
|
,COALESCE(f.C_NYOMTATASINEV, u.USERNAME) AS C_FELHASZNALONEV
|
|
,e.HOSTNAME AS C_HOSTNAME
|
|
FROM dbo.T_ENTITYHISTORY e WITH(NOLOCK)
|
|
LEFT JOIN dbo.T_ENTITYATTRIBUTEHISTORY ea WITH(NOLOCK) ON ea.C_ENTITYHISTORYID = e.ID
|
|
LEFT JOIN dbo.T_FELHASZNALO f WITH(NOLOCK) ON f.ID = e.C_FELHASZNALOID
|
|
LEFT JOIN auditlog.Users u WITH(NOLOCK) ON u.USERID = e.C_FELHASZNALOID
|
|
WHERE EXISTS(SELECT * FROM dbo.T_ENTITYATTRIBUTEHISTORY h WITH(NOLOCK) WHERE h.C_ENTITYHISTORYID = e.ID) -- performancia miatt
|
|
GO |