SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure [dbo].[p_dictionaries_init]

as
	
	SET NOCOUNT ON;

	/*
	DGE 12/03/2014
	Lors de la création ou la modification d'un dictionnaire, on ajoute automatiquement
	les enregistrements pour toutes les strings dans les tables:
	- md_dictionaries_strings;
	- md_dictionaries_tables;
	- md_dictionaries_fields;
	- md_dictionaries_fields_cpl;
	- md_dictionaries_grids.


	*/


	-- strings
	insert into md_dictionaries_strings(dicts_dictionary_code, dicts_string_name, dicts_string)
	select	distinct i.dict_dictionary_code, str_string_name, null
	from	md_strings, md_dictionaries i
	where	not exists (select	1
						from	md_dictionaries_strings
						where	dicts_dictionary_code = i.dict_dictionary_code
						and		dicts_string_name = str_string_name)


	-- tables
	insert into md_dictionaries_tables(dictt_dictionary_code, dictt_table_name, dictt_string)
	select	distinct i.dict_dictionary_code, tbl_table_name, null
	from	md_tables, md_dictionaries i
	where	not exists	(select	1
						from	md_dictionaries_tables
						where	dictt_dictionary_code = i.dict_dictionary_code
						and		dictt_table_name = tbl_table_name)

	-- fields
	insert into md_dictionaries_fields(dictf_dictionary_code, dictf_table_name, dictf_field_name, dictf_string)
	select	distinct i.dict_dictionary_code, fld_table_name, fld_field_name, null
	from	md_fields, md_dictionaries i
	where	not exists	(select 1
						from	md_dictionaries_fields
						where	dictf_dictionary_code = i.dict_dictionary_code
						and		dictf_table_name = fld_table_name
						and		dictf_field_name = fld_field_name)

	-- fields complements
	insert into md_dictionaries_fields_cpl(dictfc_dictionary_code, dictfc_table_name, dictfc_field_name, dictfc_string)
	select	distinct i.dict_dictionary_code, fld_table_name, fld_field_name, null
	from	md_fields_cpl, md_dictionaries i
	where	not exists	(select 1
						from	md_dictionaries_fields_cpl
						where	dictfc_dictionary_code = i.dict_dictionary_code
						and		dictfc_table_name = fld_table_name
						and		dictfc_field_name = fld_field_name)

	-- grids
	insert into md_dictionaries_grids(dictg_dictionary_code, dictg_grid_name, dictg_string)
	select	distinct i.dict_dictionary_code, grd_grid_name, null
	from	md_grids, md_dictionaries i
	where	not exists	(select 1
						from	md_dictionaries_grids
						where	dictg_dictionary_code = i.dict_dictionary_code
						and		dictg_grid_name = grd_grid_name)
GO
