Database conversion questions

[Tranquility 12]

Greetings,

 

Regarding Hercules' almost demanding way of using text-based database files.

Having developed mainly item and mobs in SQL on rAthena, is there's no swift way to import SQL queries to convert them to text?

 

Because of that I have been working on the database conversion, which I have said in the past and you can find the information in this topic.

Getting stuck on two aspects so far in (successfully) converting the databases if I have to stick with SQL due to the above not being (currently) possible:

1. equip_locations at rA is set to mediumint(7) and at Herc to smallint(4), which prevents conversion due to certain values being out of range, at least as far as I've researched.
   ✓ Solved this one, had to use CHANGE instead of MODIFY and re-state the column name.
    
2. The type of item where rA and Herc have opposite values for Weapon and Armor/Garment/Boots/Headgear/Accessory, updating the query to switch them yields either all weapons or all armor/etc.
   

Is anyone able and willing to push me in the right direction?

Naturally I'll publish the modifications I've done to jaBote's rA > Herc convertor when I get things are working the way it is intended to. Finally, I still haven't solved/received the answer I wanted from the topic I quoted above. It doesn't affect me or the server I'm working on, but it is for the sake of completion for the convertor for those who are affected by that specific table.

 

Kind regards,

 

Tranquility

Edited September 15, 2015 by Tranquility

[Tranquility 12]

2, Something like this

INSERT INTO hercitemdb (id, type)SELECT id,         CASE          WHEN type = 4 THEN 5         WHEN type = 5 THEN 4         ELSE type         ENDFROM raitemdb;

 

 

 

 

 

1) Had to use CHANGE instead of MODIFY and re-state the column name.

2) Ended up a bit problematic for me, but the following did work (so far):

UPDATE item_db2 SET type =  CASE   WHEN type = 4 THEN 5  WHEN type = 5 THEN 4  ELSE type  END

Basically changing your INSERT INTO > UPDATE. When trying with REPLACE INTO it'll create NULL'd fields for the other columns.

Thanks for the insight to the solution!

 

EDIT: Once I've updated rA and Herc again I'll double-check all the data & fields for conversion and aks for feedback here.

Since the idea is to maintain the current database, I'd like the process to go smooth for other persons interested in doing the emulator conversion.

Edited September 21, 2015 by Tranquility

[Tranquility 12]

After some more searching using a few search engines I'm still stuck with this issue. I admit my knowledge on SQL manipulation is very basic, but a push in the right direction is appreciated.

Not expecting the whole solution to be brought to me, but if that's done anyhow, it'll be appreciated.

 

Thus shameless bump.

[Tranquility 12]

In my attempt to gain a grasp on SQL a question arrised.

Is it possible to store all values of a certain column in an array or table, which then can be converted accordingly to the right values for the type of items?

If it is, how can I achieve this? Because that would almost finalise the issues I have so far (exception being shadow gears).

[mleo1 36]

2, Something like this

INSERT INTO hercitemdb (id, type)SELECT id,         CASE          WHEN type = 4 THEN 5         WHEN type = 5 THEN 4         ELSE type         ENDFROM raitemdb;

 

 

 

Edited September 19, 2015 by mleo1

[Tranquility 12]

Yes something like that is what I came up with, however, since it doesn't store the variables, it'll end up with the same values after the query.

Since 4 > 5 and then afterwards 5 > 4 again, resulting in a loop.

 

Which is why I was looking for a way to store it in a table or array so they could be converted individually.

Since I'm not an expert on this and (re-)learn programming and database management from the start all over again I fail to correct my mistakes so far.

 

Thanks for the effort though, you're thinking in the right direction!

[mleo1 36]

looks working for me, I ran the code, my tables are table1 instead of raitemdb, table2 instead hercitemdb



 

[Tranquility 12]

In that case I'll have to duplicate my item_db / item_db2 first to be able to apply this method I guess.

And I'll have to think of something not to erase the existing data in the other columns.

I'll give this a try again this way soon, thanks.

[mleo1 36]

Was making my own upgrader, still not finished, Still researching on differences

Heres what i find out

differences on `type`

itemdb_re in rA has atk:matk field

rA uses `attack` while herc uses `atk`

herc has this

`bindonequip`,

`forceserial`,

`buyingstore`,

`delay`,

`trade_flag`,

`trade_group`,

`nouse_flag`,

`nouse_group`,

`stack_amount`,

`stack_flag`,

`sprite`,

differences on size
 

differences on default value

etc, dunno I watch TV

@@Tranquility
youll need to update the trade_flag, buying store, etc lol, 

You'll gonna have problems in script field too, rA and Herc have differences in scripting

 

Maybe herc should create a wiki on differences lol

[Tranquility 12]

Was making my own upgrader, still not finished, Still researching on differences

 

Heres what i find out

 

differences on `type`

 

itemdb_re in rA has atk:matk field

 

rA uses `attack` while herc uses `atk`

 

herc has this

`bindonequip`,

`forceserial`,

`buyingstore`,

`delay`,

`trade_flag`,

`trade_group`,

`nouse_flag`,

`nouse_group`,

`stack_amount`,

`stack_flag`,

`sprite`,

 

differences on size

 

differences on default value

 

etc, dunno I watch TV

 

@@Tranquility

youll need to update the trade_flag, buying store, etc lol, 

 

You'll gonna have problems in script field too, rA and Herc have differences in scripting

 

 

Maybe herc should create a wiki on differences lol

 

Yes I am aware of it, seeing that I have the following query prepared:

ALTER TABLE `item_db` MODIFY `price_buy` MEDIUMINT(10) DEFAULT NULL,	MODIFY `price_sell` MEDIUMINT(10) DEFAULT NULL,	MODIFY `weight` smallint(5) UNSIGNED DEFAULT NULL,	CHANGE `attack` `atk` SMALLINT(5) UNSIGNED DEFAULT NULL,	ADD `matk` smallint(5) UNSIGNED DEFAULT NULL AFTER `atk`,	MODIFY `equip_jobs` int(12) UNSIGNED DEFAULT NULL,	MODIFY `equip_upper` tinyint(8) UNSIGNED DEFAULT NULL,	MODIFY `equip_genders` tinyint(2) UNSIGNED DEFAULT NULL,	CHANGE `equip_locations` `equip_locations` smallint(4) UNSIGNED DEFAULT NULL;	MODIFY `weapon_level` tinyint(2) UNSIGNED DEFAULT NULL,	CHANGE `equip_level` `equip_level_min` smallint(5) UNSIGNED DEFAULT NULL,	ADD `equip_level_max` smallint(5) UNSIGNED DEFAULT NULL AFTER `equip_level_min`,	MODIFY `view` smallint(3) UNSIGNED DEFAULT NULL,	ADD `bindonequip` tinyint(1) UNSIGNED DEFAULT NULL,	ADD `buyingstore` tinyint(1) UNSIGNED DEFAULT NULL,	ADD `delay` mediumint(9) UNSIGNED DEFAULT NULL,	ADD `trade_flag` smallint(4) UNSIGNED DEFAULT NULL,	ADD `trade_group` smallint(3) UNSIGNED DEFAULT NULL,	ADD `nouse_flag` smallint(4) UNSIGNED DEFAULT NULL,	ADD `nouse_group` smallint(4) UNSIGNED DEFAULT NULL,	ADD `stack_amount` mediumint(6) UNSIGNED DEFAULT NULL,	ADD `stack_flag` tinyint(2) UNSIGNED DEFAULT NULL,	ADD `sprite` mediumint(6) UNSIGNED DEFAULT NULL;

There are indeed quite some differences and it amazes me, because it's focussed around the same game.

However, since Herc seems to be a tad bit more up-to-date with clients and have a plugin system, they now have a slight preferrence. Which is why this conversion tool has to be upgraded and modified accordingly.

[Dastgir 1244]

I guess its time to upgrade the sql-script in repo....

[Tranquility 12]

I guess its time to upgrade the sql-script in repo....

It very much is, that's why I've been trying to figure it all out =P

 

For renewal based items it'll bring another issue though, shadow gears have a separate type (12) in rA, which is non-existent for Herc. Though probably using the item database from Herc itself would prove easier, it won't if people made modifications to their own database (pre-renewal server with the addition of renewal items for example).

Edited September 21, 2015 by Tranquility

[Dastgir 1244]

 

I guess its time to upgrade the sql-script in repo....

It very much is, that's why I've been trying to figure it all out =P

 

For renewal based items it'll bring another issue though, shadow gears have a separate type (12) in rA, which is non-existent for Herc. Though probably using the item database from Herc itself would prove easier, it won't if people made modifications to their own database (pre-renewal server with the addition of renewal items for example).

Hercules discourage using item_db via SQL, and support for reading SQL db will be deprecated soon.

[Tranquility 12]

 

 

I guess its time to upgrade the sql-script in repo....

It very much is, that's why I've been trying to figure it all out =P

 

For renewal based items it'll bring another issue though, shadow gears have a separate type (12) in rA, which is non-existent for Herc. Though probably using the item database from Herc itself would prove easier, it won't if people made modifications to their own database (pre-renewal server with the addition of renewal items for example).

Hercules discourage using item_db via SQL, and support for reading SQL db will be deprecated soon.

I realised that in other topics. however, is there a way to easily convert SQL databases back to txt or in Hercules' case, conf files?

Tokei's tool allows to create most things and convert it to the right format, but it isn't possible (yet) to import a SQL database from a specific emulator.

I fail to see why reading from SQL for those things will deprecate soon though, it is convenient and easy to access from phpmyadmin on people's browser, whereas the item_db on the VPS usually requires log-ins via Putty, VNC or some FTP synchronisation, which all seem more labourous to me ;p

Thanks for bringing it to our attention though, I'm just curious as to why this change is coming.