[Release] Item generator

[Maks19973]

Originally Posted by SilentWisdom

Using wrong views, original have bad coallation:

i check some ideas and i see
if i select "where id = 220100" all is ok, model_00 = 570 ( checked in Reference and 570 is right number )
but if i select all i see in id 220100 model_00 = 573 ( must be 570 as i see in Reference )
and rdb tools select all data and i get rdb with not correct data
WTF?

edit:
i try original view and new ( what you give me ) effect same, model_00 573 when select all and model_00 570 when select one row

Stupid fix for this

1st
Make new Table ItemRef

 Spoiler

USE [Arcadia94]
GO

/****** Object: Table [dbo].[ItemRef] Script Date: 21.06.2018 2:13:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ItemRef](
[id] [bigint] NULL,
[reference] [varchar](256) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

2nd Change view

 Spoiler

/* ************************************************** ************************ */
/* */
/* ::: :::::::: */
/* 94 View item.sql :+: :+: :+: */
/* +:+ +:+ +:+ */
/* By: Gangor <> +#+ +:+ +#+ */
/* +#+#+#+#+#+ +#+ */
/* Created: 2017/10/25 00:01:34 by Gangor #+# #+# */
/* Updated: 2017/10/25 00:01:34 by Gangor ### ########.fr */
/* */
/* ************************************************** ************************ */

USE [Arcadia94]
GO

/****** Object: View [dbo].[ItemReferenceResource] Script Date: 25/10/2017 00:01:34 ******/
IF OBJECT_ID('dbo.ItemReferenceResource') IS NOT NULL
BEGIN
DROP VIEW [dbo].[ItemReferenceResource]
END

/****** Object: View [dbo].[ItemReferenceResource] Script Date: 25/10/2017 00:01:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[ItemReferenceResource]
AS

WITH subrequest AS
(
SELECT id, '1' [type], reference FROM ItemResource
unpivot(
reference for referencies in (model_00, model_01, model_02, model_03, model_04, model_05, model_06, model_07, model_08, model_09, model_10, model_11, model_12, model_13, model_14, model_15, model_16, model_17)
) unpiv
UNION
SELECT id, '2' [type], reference FROM ItemResource
unpivot(
reference for referencies in (texture_filename)
) unpiv
UNION
SELECT id, '3' [type], reference FROM ItemResource
unpivot(
reference for referencies in (drop_type)
) unpiv
UNION
SELECT id, '4' [type], reference FROM ItemResource
unpivot(
reference for referencies in (icon_file_name)
) unpiv
)

SELECT
ROW_NUMBER() OVER(ORDER BY min(id), min(type)) - 1 [id],
reference
FROM subrequest
GROUP BY reference
ORDER BY min(id), min(type)
OFFSET 0 ROWS

GO

USE [Arcadia94]
GO

/****** Object: View [dbo].[Item] Script Date: 25/10/2017 00:01:34 ******/
IF OBJECT_ID('dbo.Item') IS NOT NULL
BEGIN
DROP VIEW [dbo].[Item]
END

/****** Object: View [dbo].[Item] Script Date: 25/10/2017 00:01:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[Item]
AS

SELECT [id]
,[name_id]
,[tooltip_id]
,[type]
,[group]
,[class]
,[wear_type]
,[set_id]
,[set_part_flag]
,[grade]
,[rank]
,[level]
,[enhance]
,[socket]
,[status_flag]
,[limit_deva]
,[limit_asura]
,[limit_gaia]
,[job_depth]
,[limit_fighter]
,[limit_hunter]
,[limit_magician]
,[limit_summoner]
,[use_min_level]
,[use_max_level]
,[target_min_level]
,[target_max_level]
,[range]
,[weight]
,[price]
,[huntaholic_point]
,[arena_point]
,[ethereal_durability]
,[endurance]
,[material]
,[summon_id]
,[item_use_flag]
,[available_period]
,[decrease_type]
,[throw_range]
,[base_type_0]
,[base_var1_0]
,[base_var2_0]
,[base_type_1]
,[base_var1_1]
,[base_var2_1]
,[base_type_2]
,[base_var1_2]
,[base_var2_2]
,[base_type_3]
,[base_var1_3]
,[base_var2_3]
,[opt_type_0]
,[opt_var1_0]
,[opt_var2_0]
,[opt_type_1]
,[opt_var1_1]
,[opt_var2_1]
,[opt_type_2]
,[opt_var1_2]
,[opt_var2_2]
,[opt_type_3]
,[opt_var1_3]
,[opt_var2_3]
,i.[effect_id]
,[enhance_id]
,[skill_id]
,[state_id]
,[state_level]
,[state_time]
,[cool_time]
,[cool_time_group]
,[model_type_dem]
,[model_type_def]
,[model_type_asm]
,[model_type_asf]
,[model_type_gam]
,[model_type_gaf]
,[deco_model_change]
, ( select id from dbo.ItemRef where reference = model_00) as model_00
,( select id from dbo.ItemRef where reference = model_01) as model_01
,( select id from dbo.ItemRef where reference = model_02) as model_02
,( select id from dbo.ItemRef where reference = model_03) as model_03
,( select id from dbo.ItemRef where reference = model_04) as model_04
,( select id from dbo.ItemRef where reference = model_05) as model_05
,( select id from dbo.ItemRef where reference = model_06) as model_06
,( select id from dbo.ItemRef where reference = model_07) as model_07
,( select id from dbo.ItemRef where reference = model_08) as model_08
,( select id from dbo.ItemRef where reference = model_09) as model_09
,( select id from dbo.ItemRef where reference = model_10) as model_10
,( select id from dbo.ItemRef where reference = model_11) as model_11
,( select id from dbo.ItemRef where reference = model_12) as model_12
,( select id from dbo.ItemRef where reference = model_13) as model_13
,( select id from dbo.ItemRef where reference = model_14) as model_14
,( select id from dbo.ItemRef where reference = model_15) as model_15
,( select id from dbo.ItemRef where reference = model_16) as model_16
,( select id from dbo.ItemRef where reference = model_17) as model_17
,( select id from dbo.ItemRef where reference = [texture_filename]) as texture_filename
,( select id from dbo.ItemRef where reference = [drop_type]) as drop_type
,[icon_id]
,( select id from dbo.ItemRef where reference = [icon_file_name]) as icon_file_name
,[script_text]
,[2nd_array]
,[group_bit_set]
,ISNULL([enhance01].[effect_id], 0) [effect01]
,ISNULL([enhance02].[effect_id], 0) [effect02]
,ISNULL([enhance01].[value_01], 0) [value1_1]
,ISNULL([enhance01].[value_05], 0) [value1_2]
,ISNULL([enhance01].[value_09], 0) [value1_3]
,ISNULL([enhance01].[value_13], 0) [value1_4]
,ISNULL([enhance01].[value_17], 0) [value1_5]
,ISNULL([enhance01].[value_21], 0) [value1_6]
,ISNULL([enhance01].[value_22], 0) [value1_7]
,ISNULL([enhance01].[value_23], 0) [value1_8]
,ISNULL([enhance01].[value_24], 0) [value1_9]
,ISNULL([enhance01].[value_25], 0) [value1_10]
,ISNULL([enhance02].[value_01], 0) [value2_1]
,ISNULL([enhance02].[value_05], 0) [value2_2]
,ISNULL([enhance02].[value_09], 0) [value2_3]
,ISNULL([enhance02].[value_13], 0) [value2_4]
,ISNULL([enhance02].[value_17], 0) [value2_5]
,ISNULL([enhance02].[value_21], 0) [value2_6]
,ISNULL([enhance02].[value_22], 0) [value2_7]
,ISNULL([enhance02].[value_23], 0) [value2_8]
,ISNULL([enhance02].[value_24], 0) [value2_9]
,ISNULL([enhance02].[value_25], 0) [value2_10]
FROM ItemResource i

LEFT OUTER JOIN dbo.EnhanceEffectResource enhance01 on enhance01.sid = enhance_id and enhance01.sub_id = 1
LEFT OUTER JOIN dbo.EnhanceEffectResource enhance02 on enhance02.sid = enhance_id and enhance02.sub_id = 2

GO

3rd
Use this query for clean info from itemref table and insert new data to itemref table from ItemReferenceResource view ( need do it everytime when you want make new rdb )

 Spoiler

Use Arcadia94

delete dbo.itemref
insert into dbo.itemref
select * from dbo.ItemReferenceResource

4nd Make item.rdb using item (table from view) and for item.ref using our new table ItemRef = correct ref and all works fine

i know it's really stupid, but if you dont have time make it better you can use it, because it's works

and sorry my really bad english

[ilyaslord36]

someone can update [94 View item.zip] to work for 9.6