------------------------------------------------------------------------
-- Script de création d'un jeu de test  
------------------------------------------------------------------------
-- Création d'une base de donnée
CREATE DATABASE TEST_XML 

-- Suppresion de la  base de donnée
-- DROP DATABASE TEST_XML 


USE TEST_XML
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'TEST_XML')
BEGIN

	IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[REGION]') AND type in (N'U'))
	BEGIN
		-- Création de la table REGION
		CREATE TABLE [dbo].[REGION](
			[id_region] [varchar](2) NOT NULL,
			[intitule] [varchar](255) NULL,
			[actif] [varchar](1) NULL
		 CONSTRAINT [PK_REGION] PRIMARY KEY CLUSTERED 
		(
			[id_region] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 98) ON [PRIMARY]
		) ON [PRIMARY]
	END
	
	
	IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CENTRE]') AND type in (N'U'))
	BEGIN
		-- Création de la table CENTRE
		CREATE TABLE [dbo].[CENTRE](
			[id_centre] [varchar](5) NOT NULL,
			[id_region] [varchar](2) NOT NULL,
			[intitule] [varchar](255) NULL,
			[actif] [varchar](1) NULL
		 CONSTRAINT [PK_CENTRE] PRIMARY KEY CLUSTERED 
		(
			[id_centre] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 98) ON [PRIMARY]
		) ON [PRIMARY]
	END


------------------------------------------------------------------------------------------
-- Insertion REGION et CENTRE
------------------------------------------------------------------------------------------
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[REGION]') AND type in (N'U'))
	BEGIN
		INSERT INTO [dbo].[REGION] ([id_region],[intitule],[actif]) VALUES ('01','Région ALSACE','1')
		INSERT INTO [dbo].[REGION] ([id_region],[intitule],[actif]) VALUES ('02','Région BRETAGNE','1')
	END

	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CENTRE]') AND type in (N'U'))
	BEGIN
		INSERT INTO [dbo].[CENTRE] ([id_centre],[id_region],[intitule],[actif]) VALUES  ('00001','01','Centre Albert','1')
		INSERT INTO [dbo].[CENTRE] ([id_centre],[id_region],[intitule],[actif]) VALUES  ('00002','02','Centre Jules Verne','1')
	END	

END