Heaven's Bazaar
Before we get on with this "tell all", let me first say, I do not and will not give support for any of the releases from other developers nor the server files themselves. If you have questions pertaining to either of those two items, please ask the person who developed them. I will however answer any questions that pertain to modification of the databases or any lua scripting questions. I will continue to update this when I have the free time. Updates will include all of my stuff, SQL related and scripts that I had released in the past and any that I decide to make in the future. Many of the things listed here are all based on queries and scripts I designed ages ago, if you weren't around back then, then please save your complaints for someone who cares.
Please note that the following is based on the most current server files (8.1), older versions of the server files may have difficulty operating, unless listed as backwards compatible.
The following will be documented and fully tutorial-ed for your convenience.
SQL Queries:
These queries are designed to help you sift through the massive databases to help find specific articles.
Step 1) Open
MSSQL and login.
Step 2) Select
New Query from the top menu.
Step 3)
Copy and Paste one of the following into the New Query window.
Step 4) Click
Execute from the top menu.
Replacement Queries:
Create New Player Account:
The following fields must be replaced with your own information:
- account_id
- account_name
- account_password
- account_email
Code:
USE Auth
INSERT INTO Account(account_id,account,password,email,pk_,creationDate_,updateDate_,creatorId_,updatorId_,portId_,type_,accessDate_,password2)
VALUES(account_id,'account_name','account_password','account_email',1,NULL,NULL,NULL,NULL,NULL,'com.elixir.billing.impl.ImmAccount',NULL,NULL)
Search Queries:
Items:
Lists Item ID and Item Name in Ascending order by the Item's ID.
Code:
SELECT ITEM.id, item.name_id, STRING.value
FROM Arcadia.dbo.ItemResource ITEM, Arcadia.dbo.StringResource STRING
WHERE item.name_id = string.code
ORDER BY id ASC
Quests:
Lists Quest ID and Quest Name in Ascending order by Quest's ID.
Code:
SELECT QUEST.id, quest.text_id_quest, STRING.value
FROM Arcadia.dbo.QuestResource QUEST, Arcadia.dbo.StringResource STRING
WHERE quest.text_id_quest = string.code
ORDER BY id ASC
NPCs:
Lists NPC ID and NPC Title in Ascending order by NPC's ID.
Code:
SELECT NPC.id, npc.text_id, STRING.value
FROM Arcadia.dbo.NPCResource NPC, Arcadia.dbo.StringResource STRING
WHERE npc.text_id = string.code
ORDER BY id ASC
Summons:
Lists Summon ID and Summon Name in Ascending order by Summon's ID.
Code:
SELECT SUMMON.id, summon.name_id, STRING.value
FROM Arcadia.dbo.SummonResource SUMMON, Arcadia.dbo.StringResource STRING
WHERE summon.name_id = string.code
ORDER BY id ASC
Skills:
Lists Skill ID and Skill Name in Ascending order by Skill's ID.
Code:
SELECT SKILL.id, skill.text_id, STRING.value
FROM Arcadia.dbo.SkillResource SKILL, Arcadia.dbo.StringResource STRING
WHERE skill.text_id = string.code
ORDER BY id ASC
States:
Lists State ID and State Name in Ascending order by State's ID.
Code:
SELECT STATE.state_id, state.text_id, STRING.value
FROM Arcadia.dbo.StateResource STATE, Arcadia.dbo.StringResource STRING
WHERE state.text_id = string.code
ORDER BY state_id ASC
Jobs:
Lists Job ID and Job Title in Ascending order by Job's ID.
Code:
SELECT JOB.id, job.text_id, STRING.value
FROM Arcadia.dbo.JobResource JOB, Arcadia.dbo.StringResource STRING
WHERE job.text_id = STRING.code
ORDER BY id ASC
Field Props:
Lists Field Prop ID and Field Prop Name in Ascending order by Field Prop's ID.
Code:
SELECT PROPP.id, propp.text_id, STRING.value
FROM Arcadia.dbo.FieldPropResource PROPP, Arcadia.dbo.StringResource STRING
WHERE propp.text_id = string.code
ORDER BY id ASC
Item and Drop Groups:
Lists various Item information and what Drop Groups that item is in. Replace change_item_id with the ID of the Item you wish to view.
Code:
DECLARE @ITEM_ID int = change_item_id
DECLARE @ITEM int = (SELECT id FROM ItemResource WHERE id = @ITEM_ID)
DECLARE @name_id int = (SELECT name_id FROM ItemResource WHERE id = @ITEM )
DECLARE @tooltip_id int = (SELECT tooltip_id FROM ItemResource WHERE id = @ITEM)
DECLARE @ITEM_N VARCHAR(60) = (SELECT value FROM StringResource WHERE code = @name_id)
DECLARE @ITEM_T VARCHAR(255) = (SELECT value FROM StringResource WHERE code = @tooltip_id)
SELECT id AS "Item ID",@ITEM_N AS "Item Name",@ITEM_T AS "Description",[level] AS "Level",class AS "Class Required",rank AS "Item Rank",use_min_level AS "Min Level Required",use_max_level AS "Max Level Allowed",price AS "Price" FROM itemresource where id = @ITEM
SELECT * FROM DropGroupResource WHERE
(drop_item_id_00 = @ITEM) OR (drop_item_id_01 = @ITEM) OR (drop_item_id_02 = @ITEM) OR (drop_item_id_03 = @ITEM) OR (drop_item_id_04 = @ITEM)
OR (drop_item_id_05 = @ITEM) OR (drop_item_id_06 = @ITEM) OR (drop_item_id_07 = @ITEM) OR (drop_item_id_08 = @ITEM) OR (drop_item_id_09 = @ITEM)
Note 1:
Search Terms
You can add the following just below the
WHERE statement to search for specific terms on any of the following search queries:
Quote:
|
AND value LIKE'%search_term%'
|
This can be further minipulated by adding a % between multiple terms to narrow down your search, for example: %stamina% will only search for items that have the word
Stamina in the value, where as, %stamina%saver% will search for items that have both the words
Stamina and
Saver in their values.
Note 2:
Add More Fields to Your Search
You can increase the fields that the queries pull from each table by adding into the
SELECT line, for example, say you wanted to know the
card_id as well as the
name and
id of a summon, you would simply add summon.card_id, to the
SELECT line of the query. Thus it would look like this:
Quote:
|
SELECT SUMMON.id, summon.name_id, STRING.value, summon.card_id
|
Note 3:
Search For More Than Just Names
You can even run searches on things other than an item's name, for instance, if you wanted the description instead, you would simply replace item.name_id with item.tooltip_id both in the
SELECT line and the
WHERE line, as shown here:
Quote:
SELECT ITEM.id, item.tooltip_id, STRING.value
FROM Arcadia.dbo.ItemResource ITEM, Arcadia.dbo.StringResource STRING
WHERE item.tooltip_id = string.code
|
Note 4:
Sorting Your Searches
You can sort your searches by adding the following at the end of the query:
Quote:
'something' can be any field that you have included in the
SELECT criteria, for example the item's id or even values.
LUA Scripts:
The following scripts are my own personal creations, as such anyone can use them, however, I only ask that you give me credit when you do.
Automated Buffing Systems:
Please note that these were made for the 6.3 Server Files, and while they will still work for the most part with current 7.4 and 8.1 Server Files, some functionality is not going to.
Rappelz 6.3 Scripts: Not compatible with server files above 6.3
NPC Buff System:
This a configurable NPC Buff System. You can easily modify the costs and the multiplier for each buff. The system is setup to allow 3 tiers of buff, allowing players to choose from 1 of 3 different buff multipliers. Including 12 different buffs and the option to purchase all buffs at once. The instructions for setup are in the script.
[Only registered and activated users can see links. Click Here To Register...] -- Server Files 8.1
Tid-Bits:
This is just a few things I found quite interesting, and thought you might be interested to know.
States:
| State ID | State Name | State Effect |
| 2002 | Steals MP | Recovers +520 MP with a 51% chance on strike. |
| 2003 | Absorbs HP | Recovers 3% HP based on DMG with a 100% chance. |
| 1302 | Limitless | +999 to All Main Stats. |
| 1301 | Can't Touch This | Reflects 10,000,000 DMG 100% when attacked. |
| 2051 | Hunting Creature Card | Increases creature card drop rate. |
| 314015 | Immortality | HP Cannot drop below 1; HP cannot drop below 0% |
