Stefan/Fuchs_Intranet
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
Fuchs_Intranet/Fuchs_Database/dbo/Stored Procedures/mfr__getSchema.sql
T
Stefan 10ecdfa2e4 Add Fuchs_Database SSDT project (schema source of truth) 
Adds the SQL Server Data Tools project for the fuchs_fds database — tables,
table types, functions and stored procedures that the backend calls (e.g.
fds__getInvoice, fds__merge_bankingtransactions, fds__tt__bankingtransactions,
fds__admin_getReportCatalog, fis_* auth). Build/model caches (bin, obj,
*.dbmdl, *.jfm, *.user) are git-ignored.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
2026-06-05 14:50:54 +02:00

170 lines
8.9 KiB
Transact-SQL
Raw Permalink Blame History

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[mfr__getSchema]
@info varchar(25)
,@tgttype varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARe @AS XML = [dbo].[mfr__schema]();
IF @info = 'table'
BEGIN
DECLARE @AssociationSets TABLE([#] int, [name] varchar(255), [set] varchar(255), [Association] varchar(255), [role1] varchar(255), [role2] varchar(255), [set1] varchar(255), [set2] varchar(255), [arole1] varchar(255), [arole2] varchar(255), [multi1] varchar(255), [multi2] varchar(255), [tablename] varchar(255));
DECLARE @ComplexProps TABLE([#] int, [Type] varchar(255), [typ] varchar(50), [name] varchar(255), [EntityType] varchar(255), [tablename] varchar(255));
DECLARE @NavigationProps TABLE([#] int, [Type] varchar(255), [typ] varchar(50), [name] varchar(255), [ToRole] varchar(255), [FromRole] varchar(255), [countertype] varchar(255), [counterset] varchar(255), [tablename] varchar(255), [countertable] varchar(255), [Association] varchar(255));
DECLARE @EntityTypes TABLE([#] int, [typ] varchar(50), [name] varchar(255), [set] varchar(255), [EntitySet] varchar(255), [tablename] varchar(255), [url] varchar(500), [DateColumn] varchar(25), [DateSQL] varchar(1000));
WITH XMLNAMESPACES
(
'http://schemas.microsoft.com/ado/2007/06/edmx' as edmx,
'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' as m
) , x as (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as '#'
, xD.value('@Name', 'varchar(255)') as 'set'
, xD.value('@Association', 'varchar(255)') as 'association'
, xD.value('End[1]/@Role', 'varchar(255)') as 'role1'
, xD.value('End[2]/@Role', 'varchar(255)') as 'role2'
, LOWER(xD.value('End[1]/@EntitySet', 'varchar(255)')) as 'set1'
, LOWER(xD.value('End[2]/@EntitySet', 'varchar(255)')) as 'set2'
--, xD.query('.') as 'q'
from @as.nodes('/Schema/EntityContainer/AssociationSet') as xm(xD)
) , y as (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as '#'
, xD.value('@Name', 'varchar(255)') as 'association'
, xD.value('End[1]/@Role', 'varchar(255)') as 'arole1'
, xD.value('End[2]/@Role', 'varchar(255)') as 'arole2'
, xD.value('End[1]/@Multiplicity', 'varchar(255)') as 'multi1'
, xD.value('End[2]/@Multiplicity', 'varchar(255)') as 'multi2'
--, xD.query('.') as 'q'
from @as.nodes('/Schema/Association') as xm(xD)
), z as (
select x.[#], x.[role1], x.[role2], x.[set1], x.[set2], RIGHT(x.[set],CHARINDEX('_', REVERSE(x.[set])) - 1) as [name],x.[set],x.[association], y.[arole1], y.[arole2], y.[multi1], y.[multi2]
from x LEFT JOIN y on x.association like ('%[.]' + y.[association])
)
INSERT INTO @AssociationSets
SELECT [#], z.[name],[set], [association],[role1],[role2], [set1], [set2] , [arole1], [arole2], [multi1], [multi2], 'mfr__*PartnerSet' as [tablename]
FROM z
LEFT JOIN sys.objects as o ON o.[name] like ('mfr__[*]' + (LOWER(z.[name])));
WITH XMLNAMESPACES
(
'http://schemas.microsoft.com/ado/2007/06/edmx' as edmx,
'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' as m
), et as (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as '#'
, xD.value('local-name(.)', 'varchar(50)') as [type]
, xD.value('@Name', 'varchar(255)') as [EntityType] --, xD.query('.') as 'q'
, xS.value('@Name', 'varchar(255)') as [Set]
, o.[name] as [tablename]
from @as.nodes('/Schema[1]/EntityType') as xm(xD)
JOIN @as.nodes('/Schema/EntityContainer/EntitySet') as xn(xS) ON (xS.value('@EntityType', 'varchar(255)')) like ('MFR.%[.]' + (xD.value('@Name', 'varchar(255)')))
JOIN sys.objects as o ON o.[name] like ('mfr__' + (LOWER(xS.value('@Name', 'varchar(255)'))))
union
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as '#'
, xD.value('local-name(.)', 'varchar(50)') as [type]
, xD.value('@Name', 'varchar(255)') as [EntityType] --, xD.query('.') as 'q'
, '' as [Set]
, o.[name] as [tablename]
from @as.nodes('/Schema[1]/ComplexType') as xm(xD)
JOIN sys.objects as o ON o.[name] like ('mfr__[#]' + (LOWER(xD.value('@Name', 'varchar(255)'))) + 's')
)
INSERT INTO @EntityTypes SELECT [#],[type],[EntityType],[set] = LOWER([set]), [EntitySet] = [set], [tablename], null, null, null FROM et;
WITH XMLNAMESPACES
(
'http://schemas.microsoft.com/ado/2007/06/edmx' as edmx,
'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' as m
), np as (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as '#'
, xD.value('../@Name', 'varchar(255)') as [property]
, xD.value('@Type', 'varchar(255)') as [nodetype]
, xD.value('@Name', 'varchar(255)') as [name]
from @as.nodes('/Schema[1]/EntityType/Property') as xm(xD)
)
INSERT INTO @ComplexProps
SELECT np.[#]
, [property]
, [type] = CASE WHEN [nodetype] like 'collection%' THEN 'array' ELSE '' END
, np.[name]
, [EntityType] = et.[name]
, et.[tablename]
from np LEFT JOIN @EntityTypes as et on np.[name] like (et.[name] + '%')
WHERE nodetype like '%MFR[.]%';
WITH XMLNAMESPACES
(
'http://schemas.microsoft.com/ado/2007/06/edmx' as edmx,
'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' as m
), np as (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as '#'
, xD.value('../@Name', 'varchar(255)') as [property]
, CASE WHEN a.[arole1] = xD.value('@ToRole', 'varchar(255)') AND a.[multi1] = '*' THEN 'array'
WHEN a.[arole1] = xD.value('@ToRole', 'varchar(255)') AND a.[multi1] = '0..1' THEN 'ref'
WHEN a.[arole2] = xD.value('@ToRole', 'varchar(255)') AND a.[multi2] = '*' THEN 'array'
WHEN a.[arole2] = xD.value('@ToRole', 'varchar(255)') AND a.[multi2] = '0..1' THEN 'ref'
END as [typ]
, xD.value('@Name', 'varchar(255)') as [name]
, xD.value('@ToRole', 'varchar(255)') as [ToRole]
, xD.value('@FromRole', 'varchar(255)') as [FromRole]
, CASE WHEN xD.value('@ToRole', 'varchar(255)') = a.[role2] THEN [set2] ELSE [set1] END [counterset]
, a.[tablename] --xD.value('@Relationship', 'varchar(255)') as
, a.[Association]
-- , xD.query('.') as 'q'
from @as.nodes('/Schema[1]/EntityType/NavigationProperty') as xm(xD)
LEFT JOIN @AssociationSets as a on (xD.value('@Relationship', 'varchar(255)')) like a.[Association]
)
INSERT INTO @NavigationProps SELECT np.[#], [property], np.[typ],np.[name],[ToRole], [FromRole],[countertype] = et.[name], [counterset],np.[tablename], [countertable] = et.tablename, [Association] = NULL
from np LEFT JOIN @EntityTypes as et on np.counterset = et.[set];
UPDATE et SET
[url] = [EntitySet] +
CASE WHEN EXISTS (SELECT * FROM @NavigationProps as np WHERE np.[type] = et.[name])
THEN '?$expand=' + STUFF((SELECT ',' + [name] as [text()] FROM @NavigationProps as np WHERE np.[type] = et.[name] ORDER BY [#] FOR XML PATH('')),1,1, '')
ELSE '' END
,[DateColumn] = (SELECT TOP(1) cols.[COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS as cols WHERE cols.[TABLE_NAME] = et.[tablename] AND ([COLUMN_NAME] COLLATE Latin1_General_CI_AS IN ('DateModified','DateCreated','DateOfCreation')) ORDER BY cols.[COLUMN_NAME] DESC)
,[DateSQL] = IIF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS as cols WHERE cols.[TABLE_NAME] = et.[tablename] AND ([COLUMN_NAME] COLLATE Latin1_General_CI_AS IN ('DateModified','DateOfCreation'))) = 2,
'SELECT [dbo].[ott_min_date](MAX([DateModified]),MAX([DateOfCreation])) FROM [dbo].[' + et.[tablename] + '];','')
FROM @EntityTypes as et;
SELECT * FROM @EntityTypes as et WHERE @tgttype is null or et.name = @tgttype;
SELECT * FROM @ComplexProps as cp where @tgttype is null or cp.[type] = @tgttype;
SELECT n.* FROM @NavigationProps as n WHERE @tgttype is null or n.[type] = @tgttype;
--SELECT * FROM @AssociationSets;
with tt as (
--SELECT [tablename] FROM @EntityTypes
--UNION
SELECT [tablename] FROM @ComplexProps as cp where @tgttype is null or cp.[type] = @tgttype
UNION
SELECT [tablename] FROM @NavigationProps as n WHERE @tgttype is null or n.[type] = @tgttype
UNION
SELECT [tablename] = [countertable] FROM @NavigationProps as n WHERE @tgttype is null or n.[type] = @tgttype
)
SELECT DISTINCT [tablename] from tt
where not exists(SELECT * FROM @EntityTypes as et where et.tablename = tt.[tablename] AND (@tgttype is null or et.name = @tgttype));
END
ELSE IF @info = 'tabledef'
BEGIN
DECLARE @types TABLE ([edm] varchar(100), [sql] varchar(50));
INSERT INTO @types VALUES ('Edm.Boolean', 'bit'),
('Edm.DateTime', 'DateTime'),
('Edm.Decimal', 'decimal'),
('Edm.Double', 'float'),
('Edm.Int32', 'int'),
('Edm.Int64', 'bigint'),
('Edm.String', 'varchar(255)');
END
END
Reference in New Issue View Git Blame Copy Permalink