-- Name:
-- sp_ORAvalidatecache
-- Description:
-- Validate distributor side meta data against current Oracle meta data
-- for a given published table.
-- Inputs:
-- @publisher == name of Oracle publisher
-- @publication == name of publication
-- @article == name of article
-- @owner == table owner
-- @tablename == table name
-- Returns:
-- Return code (0 for success, 1 for failure)
-- Result set
-- column description
-- publication publication name
-- article article name
-- tableowner table owner
-- tablename table name
-- columnname column name
-- index index or constraint name
-- column_ordinal column ordinal
-- type column, index, or constrinat type
-- length length
-- prec precision
-- scale scale
-- isnullable is nullable
-- validated is validated
-- consstatus constraint status
-- idxstatus index status
-- description 'Column dropped'
-- 'Column added'
-- 'Column changed from'
-- 'Column changed to'
-- 'index dropped'
-- 'index added'
-- 'index changed from'
-- 'index changed to'
-- 'Constraint dropped'
-- 'Constraint added'
-- 'Constraint changed from'
-- 'Constraint changed to'
-- Security:
-- internal stored procedure
-- Requires Certificate signature for catalog access
CREATE PROCEDURE sys.sp_ORAvalidatecache
(
@publisher sysname,
@publication sysname = NULL,
@article sysname = NULL,
@owner sysname = NULL,
@tablename sysname = NULL
)
AS
BEGIN
DECLARE @retcode int
DECLARE @pubid int
DECLARE @table_id int
DECLARE @cntcache int
DECLARE @cntmatch int
DECLARE @cntactual int
-- Get publisher ID
SELECT @pubid = srvid
FROM master.dbo.sysservers
WHERE UPPER(srvname collate database_default) = UPPER(@publisher) collate database_default
IF @pubid IS NULL
BEGIN
RAISERROR(21600, 16, -1, @publisher)
RETURN (1)
END
-- Table name and owner must both be non-NULL
IF @tablename IS NULL OR @owner IS NULL
BEGIN
-- Both table name and owner must be NON NULL
RAISERROR('Both table name and table owner must be NON NULL.', 16, -1)
RETURN (1)
END
-- Verify that table is currently published
-- and determine table ID
SELECT distinct @table_id = iha.table_id
FROM IHarticles iha, MSarticles msa
WHERE msa.source_owner = @owner
AND msa.source_object = @tablename
AND msa.publisher_id = @pubid
AND msa.publisher_id = iha.publisher_id
AND msa.article_id = iha.article_id
AND msa.publication_id = iha.publication_id
IF @table_id IS NULL
BEGIN
-- Table is not currently published
RAISERROR('Table is not currently published.', 16, -1)
RETURN (1)
END
-- Create the temporary meta data tables
CREATE TABLE #publishercolumns
(
name sysname collate database_default,
column_ordinal int,
type sysname collate database_default,
length int,
prec int,
scale int,
isnullable bit,
iscaptured bit
)
CREATE TABLE #indexes
(
index_name nvarchar(30),
index_owner nvarchar(30),
uniqueness nvarchar(11),
column_name nvarchar(30),
column_position int,
index_status nvarchar(8)
)
CREATE TABLE #constraints
(
constraint_name nvarchar(30),
constraint_type nchar(1),
table_owner nvarchar(30),
table_name nvarchar(30),
index_owner nvarchar(30),
index_name nvarchar(30),
column_name nvarchar(30),
column_position int,
validated nvarchar(13),
cons_status nvarchar(8),
idx_status nvarchar(8)
)
CREATE TABLE #MATCHES (name NVARCHAR(30))
CREATE TABLE #MATCHES1(name NVARCHAR(100), column_name NVARCHAR(30), index_name NVARCHAR(30))
CREATE TABLE #MATCHES2(name NVARCHAR(100), column_name NVARCHAR(30), index_name NVARCHAR(30))
-- Populate the temporary metadata tables
EXEC @retcode = sys.sp_ORAgetcolumndata
@owner = @owner,
@tablename = @tablename,
@publisher = @publisher,
@fordiagnosis = 1
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
RAISERROR (21781, 16, -1)
RETURN (1)
END
-- Determine the number of cached columns in the published table
SELECT @cntcache = COUNT(*)
FROM IHpublishercolumns
WHERE table_id = @table_id
AND publisher_id = @pubid
-- Determine the number of actual columns in the published table
SELECT @cntactual = COUNT(*)
FROM #publishercolumns
-- Compare the data in the temporary tables with that in the meta data cache
INSERT INTO #MATCHES
SELECT ihpc.name
FROM IHpublishercolumns ihpc, #publishercolumns tpc
WHERE ihpc.table_id = @table_id
AND ihpc.publisher_id = @pubid
AND ihpc.name = tpc.name
AND ihpc.column_ordinal = tpc.column_ordinal
AND ihpc.type = tpc.type
AND (ihpc.length = tpc.length OR (ihpc.length IS NULL and tpc.length IS NULL))
AND (ihpc.prec = tpc.prec OR (ihpc.prec IS NULL and tpc.prec IS NULL))
AND (ihpc.scale = tpc.scale OR (ihpc.scale IS NULL and tpc.scale IS NULL))
AND ihpc.isnullable = tpc.isnullable
-- Determine the number of matched columns in the table
SELECT @cntmatch = COUNT(*)
FROM #MATCHES
-- If the counts don't match, populate result table
if @cntcache <> @cntmatch OR @cntcache <> @cntactual
BEGIN
-- Determine which entries in IHpublishercolumns do not
-- have matching entries in #publishercolumns
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, name, NULL, column_ordinal, type, length, prec, scale, isnullable, NULL, NULL, NULL, N'Column dropped'
FROM IHpublishercolumns
WHERE table_id = @table_id
AND publisher_id = @pubid
AND name NOT IN (SELECT * from #MATCHES)
AND name NOT IN (SELECT name from #publishercolumns)
-- Determine which entries in #publishercolumns do not
-- have matching entries in IHpublishercolumns
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, name, NULL, column_ordinal, type, length, prec, scale, isnullable, NULL, NULL, NULL, N'Column added'
FROM #publishercolumns
WHERE name NOT IN (SELECT * from #MATCHES)
AND name NOT IN (SELECT name from IHpublishercolumns
WHERE table_id = @table_id
AND publisher_id = @pubid)
-- Determine which entries in IHpublishercolumns have
-- changed entries in #publishercolumns
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, name, NULL, column_ordinal, type, length, prec, scale, isnullable, NULL, NULL, NULL, N'Column changed from'
FROM IHpublishercolumns
WHERE table_id = @table_id
AND publisher_id = @pubid
AND name NOT IN (SELECT * from #MATCHES)
AND name IN (SELECT name from #publishercolumns)
AND name IN (SELECT name from IHpublishercolumns)
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, name, NULL, column_ordinal, type, length, prec, scale, isnullable, NULL, NULL, NULL, N'Column changed to'
FROM #publishercolumns
WHERE name NOT IN (SELECT * from #MATCHES)
AND name IN (SELECT name from #publishercolumns)
AND name IN (SELECT name from IHpublishercolumns
WHERE table_id = @table_id
AND publisher_id = @pubid)
END
-- Compare #indexes with data in IHpublisherindexes
SELECT @cntcache = COUNT(*)
FROM IHpublisherindexes
WHERE table_id = @table_id
AND publisher_id = @pubid
-- Determine the number of actual indexes for the published table
SELECT @cntactual = COUNT(*)
FROM #indexes
-- Compare the data in the temporary tables with that in the meta data cache
DELETE #MATCHES
INSERT INTO #MATCHES
SELECT distinct ihpi.name
FROM IHpublisherindexes ihpi, #indexes tpi
WHERE ihpi.table_id = @table_id
AND ihpi.publisher_id = @pubid
AND ihpi.name = tpi.index_name
AND ihpi.type = tpi.uniqueness
AND 'VALID' = tpi.index_status
-- Determine the number of matched columns in the table
SELECT @cntmatch = COUNT(*)
FROM #MATCHES
-- If the counts don't match, populate result table
if @cntcache <> @cntmatch OR @cntcache <> @cntactual
BEGIN
-- Determine which entries in IHpublisherindexes do not
-- have matching entries in #indexes
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, NULL, name, NULL, type, NULL, NULL, NULL, NULL, NULL, NULL, 'VALID', N'Index dropped'
FROM IHpublisherindexes
WHERE table_id = @table_id
AND publisher_id = @pubid
AND name NOT IN (SELECT * from #MATCHES)
AND name NOT IN (SELECT index_name from #indexes)
-- Determine which entries in #indexes do not
-- have matching entries in IHpublisherindexes
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, NULL, index_name, NULL, uniqueness, NULL, NULL, NULL, NULL, NULL, NULL, index_status, N'Index added'
FROM #indexes
WHERE index_name NOT IN (SELECT * from #MATCHES)
AND index_name NOT IN
(SELECT name from IHpublisherindexes
WHERE table_id = @table_id
AND publisher_id = @pubid)
-- Determine which entries in IHpublisherindexes have
-- changed entries in #indexes
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, NULL, name, NULL, type, NULL, NULL, NULL, NULL, NULL, NULL, 'VALID', N'Index changed from'
FROM IHpublisherindexes
WHERE name NOT IN (SELECT * from #MATCHES)
AND name IN (SELECT index_name from #indexes)
AND name IN
(SELECT name from IHpublisherindexes
WHERE table_id = @table_id
AND publisher_id = @pubid)
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, NULL, index_name, NULL, uniqueness, NULL, NULL, NULL, NULL, NULL, NULL, index_status, N'Index changed to'
FROM #indexes
WHERE index_name NOT IN (SELECT * from #MATCHES)
AND index_name IN (SELECT index_name from #indexes)
AND index_name IN
(SELECT name from IHpublisherindexes
WHERE table_id = @table_id
AND publisher_id = @pubid)
END
-- Compare #constraints with data in IHpublisherconstraints
SELECT @cntcache = COUNT(*)
FROM IHpublisherconstraints
WHERE table_id = @table_id
AND publisher_id = @pubid
-- Determine the number of actual indexes for the published table
SELECT @cntactual = COUNT(*)
FROM #constraints
-- Compare the data in the temporary tables with that in the meta data cache
DELETE #MATCHES
INSERT INTO #MATCHES
SELECT distinct ihpc.name
FROM IHpublisherconstraints ihpc, #constraints tpc
WHERE ihpc.table_id = @table_id
AND ihpc.publisher_id = @pubid
AND ihpc.name = tpc.constraint_name
AND 'VALIDATED' = tpc.validated
AND 'ENABLED' = tpc.cons_status
-- For Oracle 8.05, we may not be able to identify an index
-- for the primary key constraint, so status may be NULL
AND (tpc.idx_status is NULL OR 'VALID' = tpc.idx_status)
-- Determine the number of matched columns in the table
SELECT @cntmatch = COUNT(*)
FROM #MATCHES
-- If the counts don't match, populate result table
if @cntcache <> @cntmatch OR @cntcache <> @cntactual
BEGIN
-- Determine which entries in IHpublisherconstraints do not
-- have matching entries in #constraints
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, NULL, name, NULL, type, NULL, NULL, NULL, NULL, 'VALIDATED', 'ENABLED', 'VALID', N'Constraint dropped'
FROM IHpublisherconstraints
WHERE table_id = @table_id
AND publisher_id = @pubid
AND name NOT IN (SELECT * from #MATCHES)
AND name NOT IN (SELECT constraint_name from #constraints)
-- Determine which entries in #constraints do not
-- have matching entries in IHpublisherconstraints
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, NULL, constraint_name, NULL, 'PRIMARYKEY', NULL, NULL, NULL, NULL, validated, cons_status, idx_status, N'Constraint Added'
FROM #constraints
WHERE constraint_name NOT IN (SELECT * from #MATCHES)
AND constraint_name NOT IN
(SELECT name from IHpublisherconstraints
WHERE table_id = @table_id
AND publisher_id = @pubid)
-- Determine which entries in IHpublisherconstraints have
-- changed entries in #constraints
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, NULL, name, NULL, type, NULL, NULL, NULL, NULL, 'VALIDATED', 'ENABLED', 'VALID', N'Constraint changed from'
FROM IHpublisherconstraints
WHERE name NOT IN (SELECT * from #MATCHES)
AND name IN (SELECT constraint_name from #constraints)
AND name IN
(SELECT name from IHpublisherconstraints
WHERE table_id = @table_id
AND publisher_id = @pubid)
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, NULL, constraint_name, NULL, 'PRIMARYKEY', NULL, NULL, NULL, NULL, validated, cons_status, idx_status, N'Constraint changed to'
FROM #constraints
WHERE constraint_name NOT IN (SELECT * from #MATCHES)
AND constraint_name IN (SELECT constraint_name from #constraints)
AND constraint_name IN
(SELECT name from IHpublisherconstraints
WHERE table_id = @table_id
AND publisher_id = @pubid)
END
-- Get the current column names and their associated constraint names
INSERT INTO #MATCHES1
SELECT c.column_name + '"' + c.constraint_name, c.column_name, c.constraint_name
FROM #constraints c
-- Get the cached column names and their associated constraint names
INSERT INTO #MATCHES2
SELECT ihpc.name + '"' + ihpi.name, ihpc.name, ihpi.name
FROM IHpublisherconstraints ihpi,
IHpublishercolumns ihpc,
IHpublishercolumnconstraints ihpci
WHERE ihpi.table_id = @table_id
AND ihpi.publisher_id = @pubid
AND ihpi.publisherconstraint_id = ihpci.publisherconstraint_id
AND ihpc.publishercolumn_id = ihpci.publishercolumn_id
AND ihpc.table_id = ihpi.table_id
AND ihpc.publisher_id = ihpi.publisher_id
-- Determine the number of matched columns in the table
SELECT @cntactual = COUNT(*)
FROM #MATCHES1
SELECT @cntcache = COUNT(*)
FROM #MATCHES2
SELECT @cntmatch = COUNT(*)
FROM #MATCHES1 M1, #MATCHES2 M2
WHERE M1.name = M2.name
AND M1.column_name = M2.column_name
AND M1.index_name = M2.index_name
-- If the counts don't match, populate result table
if @cntcache <> @cntmatch OR @cntcache <> @cntactual
BEGIN
-- Determine which index columns have been dropped
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, column_name, index_name, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'Constraint column dropped'
FROM #MATCHES2
WHERE name NOT IN (SELECT name from #MATCHES1)
-- Determine which index columns have been added
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, column_name, index_name, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'Constraint column added'
FROM #MATCHES1
WHERE name NOT IN (SELECT name from #MATCHES2)
END
-- Get the current column names and their associated index names
DELETE #MATCHES1
INSERT INTO #MATCHES1
SELECT c.column_name + '"' + c.index_name, c.column_name, c.index_name
FROM #indexes c
-- Get the cached column names and their associated index names
DELETE #MATCHES2
INSERT INTO #MATCHES2
SELECT ihpc.name + '"' + ihpi.name, ihpc.name, ihpi.name
FROM IHpublisherindexes ihpi,
IHpublishercolumns ihpc,
IHpublishercolumnindexes ihpci
WHERE ihpi.table_id = @table_id
AND ihpi.publisher_id = @pubid
AND ihpi.publisherindex_id = ihpci.publisherindex_id
AND ihpc.publishercolumn_id = ihpci.publishercolumn_id
AND ihpc.table_id = ihpi.table_id
AND ihpc.publisher_id = ihpi.publisher_id
-- Determine the number of matched columns in the table
SELECT @cntactual = COUNT(*)
FROM #MATCHES1
SELECT @cntcache = COUNT(*)
FROM #MATCHES2
SELECT @cntmatch = COUNT(*)
FROM #MATCHES1 M1, #MATCHES2 M2
WHERE M1.name = M2.name
AND M1.column_name = M2.column_name
AND M1.index_name = M2.index_name
-- If the counts don't match, populate result table
if @cntcache <> @cntmatch OR @cntcache <> @cntactual
BEGIN
-- Determine which index columns have been dropped
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, column_name, index_name, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'Index column dropped'
FROM #MATCHES2
WHERE name NOT IN (SELECT name from #MATCHES1)
-- Determine which index columns have been added
INSERT INTO #RESULTSET
SELECT @publication, @article, @owner, @tablename, column_name, index_name, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'Index column added'
FROM #MATCHES1
WHERE name NOT IN (SELECT name from #MATCHES2)
END
RETURN (0)
END
