· 4 years ago · Feb 15, 2021, 09:40 PM
1-- ===========================================================================
2-- Description: Resolves the incoming possibly wildcarded path, by computing the
3-- full source and sink locations, including the object namespace
4-- and object name. The information is written to a lookup table
5-- for downstream activities.
6-- Tables: [dc].[D_PhysicalObject]
7--
8-- Parameters:
9-- @source_storage_location_sid - TODO
10-- @source_namespace - TODO
11-- @source_name - TODO
12-- @sink_storage_location_sid - TODO
13-- @sink_namespace - TODO
14-- @sink_name - TODO
15-- @target_hierarchy - TODO
16-- @target_extension - TODO
17--
18-- Returns:
19-- Nothing
20-- ===========================================================================
21CREATE PROCEDURE [dc].[spResolveWildcardPath]
22 @pipeline_run_sid INT,
23 @source_storage_location_sid INT,
24 @source_namespace NVARCHAR(255),
25 @source_name NVARCHAR(255),
26 @sink_storage_location_sid INT,
27 @sink_namespace NVARCHAR(255),
28 @sink_name NVARCHAR(255),
29 @target_hierarchy NVARCHAR(10)
30AS
31BEGIN
32 SET NOCOUNT ON;
33
34 DECLARE @source_namespace_like NVARCHAR(255) = REPLACE(@source_namespace, '*', '%')
35 DECLARE @source_name_like NVARCHAR(255) = REPLACE(@source_name, '*', '%');
36
37 SELECT
38 @pipeline_run_sid AS [pipeline_run_fid],
39 [object_sid] AS [source_object_fid],
40 @source_namespace AS [source_wildcard_namespace],
41 @source_name AS [source_wildcard_name],
42 @sink_storage_location_sid AS [sink_storage_location_fid],
43 [dc].[fnResolveSinkNamespace]
44 (
45 @source_namespace,
46 [object_name],
47 @sink_namespace,
48 @target_hierarchy
49 ) AS [sink_namespace],
50 CASE
51 WHEN @sink_name = '' THEN @source_name -- Do not rename the object
52 ELSE @sink_name -- Rename the object
53 END AS [sink_name],
54 -2 AS [sink_schema_sid] -- Not yet implemented
55 INTO #tempdb
56 FROM [dc].[F_PhysicalObject]
57 WHERE [storage_location_fid] = @source_storage_location_sid
58 AND [object_namespace] LIKE @source_namespace_like
59 AND [object_name] LIKE @source_name_like
60 AND SYSDATETIME() BETWEEN [valid_from] AND [valid_to]
61
62 IF EXISTS (SELECT * FROM #tempdb)
63 BEGIN
64 INSERT INTO [dc].[D_WriteAheadLog]
65 (
66 [pipeline_run_fid],
67 [source_object_fid],
68 [source_wildcard_namespace],
69 [source_wildcard_name],
70 [sink_storage_location_fid],
71 [sink_namespace],
72 [sink_name],
73 [sink_schema_fid]
74 )
75 SELECT
76 [pipeline_run_fid],
77 [source_object_fid],
78 [source_wildcard_namespace],
79 [source_wildcard_name],
80 [sink_storage_location_fid],
81 [sink_namespace],
82 [sink_name],
83 [sink_schema_sid]
84 FROM #tempdb
85 END
86 ELSE
87 BEGIN
88 INSERT INTO [dc].[D_WriteAheadLog]
89 (
90 [pipeline_run_fid],
91 [source_object_fid],
92 [source_wildcard_namespace],
93 [source_wildcard_name],
94 [sink_storage_location_fid],
95 [sink_namespace],
96 [sink_name],
97 [sink_schema_fid]
98 )
99 SELECT
100 @pipeline_run_sid AS [pipeline_run_fid],
101 -2 AS [source_object_fid],
102 @source_namespace AS [source_wildcard_namespace],
103 @source_name AS [source_wildcard_name],
104 @sink_storage_location_sid AS [sink_storage_location_fid],
105 [dc].[fnResolveSinkNamespace]
106 (
107 @source_namespace,
108 @source_name,
109 @sink_namespace,
110 @target_hierarchy
111 ) AS [sink_namespace],
112 CASE
113 WHEN @sink_name = '' THEN @source_name -- Do not rename the object
114 ELSE @sink_name -- Rename the object
115 END AS [sink_name],
116 -2 AS [sink_schema_sid] -- Not yet implemented
117 END
118END