Create database Metabase
go
Use Metabase
go
exec sp_addrole 'SD'
go

CREATE TABLE [SD].[Nodes] (
	[Node] [varchar] (50) ,
	[Type] [varchar] (50)  
) ON [PRIMARY]
GO

CREATE TABLE [SD].[SDB] (
	[SDB_Name] [varchar] (50)  ,
	[Node] [varchar] (50)  ,
	[NDBType] [varchar] (50)  
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE sd_alter_node @node varchar(50), @query_type varchar(50) AS

declare @alter varchar(50), @type varchar(50), @current_type varchar(50)
select @type=SUBSTRING (@query_type , CHARINDEX(' ',@query_type)+1 , 1000 )
select  CHARINDEX(' ',@query_type) 
select @alter=substring (@query_type,1,len(@query_type)-len(@type))

select @current_type= (select type from  metabase.sd.nodes where node=@node)

if @alter='add ' 
begin
if (@type='client') and (@current_type='server')  update metabase.sd.nodes set type='peer' where node=@node
if (@type='server') and (@current_type='client')  update metabase.sd.nodes set type='peer' where node=@node
end
else if @alter='drop' 
if (@type='client') and (@current_type='peer')  update metabase.sd.nodes set type='server' where node=@node
if (@type='server') and (@current_type='peer')  update metabase.sd.nodes set type='client' where node=@node

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE sd_create_node  @node varchar(50), @type varchar(50)='peer'   AS

declare @script varchar(5000),@exec varchar(50), @query_exec varchar(5000)

insert into metabase.sd.nodes (node,type) values (@node,@type)

set @exec=@node+'.master..xp_cmdshell' 
set @script='osql /U sa /P /d msdb < c:\script\msdb.sql'

set @query_exec =
'execute '+@exec+ ' '''+@script+''' '
exec(@query_exec)




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE sd_create_node_database @db varchar(50), @node varchar(50), @type varchar(50)  AS

declare @query varchar(5000),@data varchar(50),@sdb_node varchar(50) 

select @sdb_node = (select node from sd.sdb where sdb_name=@db)

set @query=' exec '+@node+'.msdb.dbo.create_ndb  '''+@sdb_node+''', '''+@db+''', '''+@type+''' '

exec (@query)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE sd_create_scalable_database @db varchar(50),@node varchar(50), @type varchar(50), @extent int   AS

declare @query varchar(5000),@data varchar(5000), @node_extent varchar(50), @msdb varchar (5000)

set @msdb='insert into '+@node+'.msdb.dbo.metabase (node) values ('''+@@servername+''')'
exec (@msdb)
set @query='
exec '+@node+'.msdb.dbo.create_sdb  '''+@db+''', '''+@type+'''
'
exec (@query)

set @data='
insert into metabase.sd.sdb values ('''+@db+ ''','''+@node+ ''','''+@type+''')
'
exec (@data)
while (@extent >1)
begin
set @extent=@extent-1
select @node_extent=node from sd.nodes where type=@type and node!=@node
exec sd_create_node_database @db,@node_extent, @type
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE sd_drop_node  @node varchar(50)  AS
declare @query varchar(5000), @ndb varchar(50), @ndb_query varchar(5000), @drop_ndb varchar(5000)

create table #ndb (sdb_name varchar(50))
set @ndb_query=
'insert into #ndb select sdb_name from sd.sdb where node= '''+@node+ '''  '
exec( @ndb_query)

if ( exists (select * from #ndb))
begin
While ( exists (select * from #ndb))
 BEGIN
  set @ndb=(select top 1 sdb_name from #ndb)
  set @drop_ndb =  'exec sd_drop_node_database   '''+@ndb+''',  '''+@node+'''  '
   exec (@drop_ndb)
   delete from #ndb  where sdb_name=@ndb
  END

end


delete from metabase.sd.nodes where node=@node

set @query=' exec '+@node+'.msdb.dbo.drop_node  '''+@node+''' '

exec (@query)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE sd_drop_node_database @sdb varchar(50), @node varchar(50)  AS

declare @query varchar(5000),@data varchar(50),@sdb_node varchar(50), @type varchar(50), @query_type varchar(5000), @query_image varchar(5000), @image varchar(50),
@q varchar(5000), @query_node varchar(5000), @new_node varchar(50), @insert varchar(5000)

select @sdb_node = (select node from sd.sdb where sdb_name=@sdb)

create table #type (NDBtype varchar(50))
set @query_type =' insert into #type select NDBtype from sd.sdb where node= '''+@node+ ''' '
exec (@query_type)
set @type=(select NDBtype from #type)
if (@type='client')
begin
create table #image (name varchar(50), type varchar(50), primnd varchar(50),size int)
set @query_image =' insert into #image select *  from '+@node+'.'+@sdb+'.sd.image '
exec (@query_image)

if ( exists (select * from #image)) 
While ( exists (select * from #image)) 
 BEGIN
  set @image=(select top 1 name from #image)
  set @q =  ' Execute '+@node+'.'+@sdb+ '.dbo.sd_drop_table '''+@image+'''  '
  exec (@q)
    
 delete from #image where name=@image

  end
end
else if (@type='server')
begin
create table #node (node varchar(50))
set @query_node =' insert into #node select node from metabase.sd.nodes  where node not in (select node from '+@sdb_node+ '.' +@sdb+ '.sd.ndb) '
exec (@query_node)
set @new_node=(select node from #node)

if (@new_node is not null) --le prendre comme noeud pour la création d'une nouvelle NDB et lui transférer les données de la NDB à supprimer
begin
exec sd_create_node_database @sdb, @new_node, 'server'
set @insert ='
insert into '+@new_node+'.' +@sdb+'.sd.prim select * from ' +@node+'.' +@sdb+'.sd.prim
insert into '+@new_node+'.' +@sdb+'.sd.size select * from ' +@node+'.' +@sdb+'.sd.size
'
end
end

set @query=' exec '+@node+'.msdb.dbo.drop_ndb '''+@sdb+''' 
                     delete from  '+@sdb_node+'.'+@sdb+'.sd.ndb where node= '''+@node+ ''' '
exec (@query)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE sd_drop_scalable_database @sdb varchar(50) AS

declare @sdb_node varchar(50),@query varchar(5000),@ndb varchar(50),@q varchar(5000)


select @sdb_node=(select node from metabase.sd.sdb where sdb_name=@sdb)

create table #ndb (node varchar(50))
set @query='
insert into #ndb select node from '+@sdb_node+'.' +@sdb+'.sd.ndb
'
exec (@query)


if ( exists (select * from #ndb)) 
While ( exists (select * from #ndb)) 
 BEGIN
  set @ndb=(select top 1 node from #ndb)
  set @q = 
          '
           exec '+@ndb+'.msdb.dbo.drop_ndb '''+@sdb+'''
                 '
--delete from  '+@sdb_node+'.'+@sdb+'.sd.ndb where node=  '''+@ndb+ ''' 
  exec (@q)
    
 delete from #ndb where node=@ndb

 END

delete from metabase.sd.sdb where sdb_name=@sdb
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

